none
Data Parsing RRS feed

  • Question

  • What would be the best way to parse data multiple times from a column?

    ABC EFG HIK123456789 01

    When this substring is run, we get some of the data 

    LTRIM(RTRIM(substring(column, CHARINDEX(' ', column, CHARINDEX(' ', column)+1), 5)))

    HIK123456789

    But how do we get the last two numbers (01) from the string?

    Monday, October 18, 2010 4:16 PM

Answers

  • You are on to the right idea.  Try adding the REVERSE function with a single CHARINDEX and see if you can get there from here.
    Monday, October 18, 2010 4:18 PM
    Moderator
  • Here is one way

     

    DECLARE @v VARCHAR(MAX) = 'ABC EFG HIK123456789 01'
    SELECT REVERSE(SUBSTRING(REVERSE(@v),1,PATINDEX('% %',REVERSE(@v))-1))
    
    


    Abdallah El-Chal, PMP, ITIL, MCTS
    Monday, October 18, 2010 4:20 PM

All replies

  • You are on to the right idea.  Try adding the REVERSE function with a single CHARINDEX and see if you can get there from here.
    Monday, October 18, 2010 4:18 PM
    Moderator
  • Do you want to parse data based on the space in the column and you don't know how many "chunks" of data you have? If so, you may use any of the string splitting techniques available. Take a look at links listed in this blog post:

    Passing multiple ranges to stored procedure

    (links listed at the top of the blog, as well as one of the common functions (based on Numbers table) is shown there also).


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, October 18, 2010 4:19 PM
    Moderator
  • Here is one way

     

    DECLARE @v VARCHAR(MAX) = 'ABC EFG HIK123456789 01'
    SELECT REVERSE(SUBSTRING(REVERSE(@v),1,PATINDEX('% %',REVERSE(@v))-1))
    
    


    Abdallah El-Chal, PMP, ITIL, MCTS
    Monday, October 18, 2010 4:20 PM
  • The other thing that you might have to worry about are trailing spaces.  You might need to use an RTRIM to remove potential trailing spaces.
    Monday, October 18, 2010 4:22 PM
    Moderator
  • I was thinking this would be a lot easier. Sadly I'm a noob on this. Does it matter that this is a view and not a table?

    For Noam, the data appears to be around the same size of chunks, after looking at the article you referenced, honestly...i wouldn't even know where to start. 

    For Abdshall, it wants me to declare a local variable. I remember seeing a SET command, but that didn't work when I tried assigning the field to be parsed.

     

     

    Monday, October 18, 2010 4:47 PM
  • Can you show some of the records in the view and what is the desired output? In that blog I showed how to create a numbers table and how to create fnSplit function. Once you have both, you may do something like

    select V.*, F.Value as LastInfo from myView V CROSS APPLY dbo.fnSplit(V.StringValue,' ') as F -- this will transpose all chunks into a separate record. If we only need last value, then we can simply do what Abdallah was showing, e.g.

     

    select V.*, REVERSE(SUBSTRING(REVERSE(V.StringData),1,PATINDEX('% %',REVERSE(V.StringData))-1)) as LastChunk from myView V
    
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, October 18, 2010 4:59 PM
    Moderator
  • Does this help?

     

    SELECT SUBSTRING([Column],DATALENGTH([Column])-PATINDEX('%[0-9][0-9]%',REVERSE([Column])),2)
    
    

     

    Dave

    Monday, October 18, 2010 5:18 PM