CHARINDEX Nth positions parsing.

Posted on 2012-08-23
Last Modified: 2012-08-28
I'm parsing a column of text which separates the data by "." - VALUE1.VALUE2.VALUE3.VALUE4

I can get VALUE4 using REVERSE but I can not isolate the middle 2 & 3.  

The closest I've gotten is with SELECT SUBSTRING(COLUMN, CHARINDEX('.',COLUMN) + 1, CHARINDEX('.',COLUMN)+CHARINDEX('.',COLUMN))  which returns VALUE2.VALUE3.

Ideally I want to substring each separately so I can assign individual names for them.

Thanks in advanced,
Question by:czlong
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    try this logic

    create function fn_ParseCSVString
    @CSVString       varchar(8000) ,
    @Delimiter      varchar(10)
    returns @tbl table (s varchar(1000))
    select * from dbo.fn_ParseCSVString ('qwe,c,rew,c,wer', ',c,')
    declare @i int ,
          @j int
          select       @i = 1
          while @i <= len(@CSVString)
                select      @j = charindex(@Delimiter, @CSVString, @i)
                if @j = 0
                      select      @j = len(@CSVString) + 1
                insert      @tbl select substring(@CSVString, @i, @j - @i)
                select      @i = @j + len(@Delimiter)
    LVL 31

    Expert Comment

    LVL 13

    Expert Comment

    If you are using ASE15.7 you can use optional start parameter for charindex:

    charindex(expression1, expression2 [, start])

    SELECT SUBSTRING(COLUMN, 1, CHARINDEX('.', COLUMN + '.', charindex('.', COLUMN)+1) - 1)

    Author Comment

    @alpmoon, I tried that before I posted and that option isn't available to me.  That would have made this light work.

    Author Comment

    @awking00, Same this as @alpmoon suggestion. My CHARINDEX only takes 2 args.
    LVL 25

    Accepted Solution

    I am loathe to actually post this.  I think a custom function (UDF) would be way better than this monstrosity ... but any ... read at your own peril.
    select col
         , substring(col,                                          -- value
                     1,                                            -- start: 1
                     charindex('.', col) - 1)                      -- length: position of first '.' in value - 1
                  as first_piece
         , substring(col,                                          -- value
                     charindex('.', col) + 1,                      -- start: first '.' in value + 1
                     charindex('.',                                -- length: first '.' in remaining string after first '.' - 1
                               substring(col, charindex('.', col) + 1, 999)) - 1)  
                  as second_piece
         , substring(col,                                          -- value
                     charindex('.',                                -- start: first '.' in remaining string after first '.' + 1
                               substring(col, charindex('.', col) + 1, 999)) 
                               + charindex('.', col) + 1,          --        plus position of first '.' in original string
                     len(col)                                      -- length: length of full value
                     - charindex('.', col)                         --         less length of first piece
                     - charindex('.', substring(col, charindex('.', col) + 1, 999)) -- less length of second piece
                     - charindex('.', reverse(col)))               --         less length of last piece
                   as third_piece
         , substring(col,                                          -- value
                     len(col) - charindex('.', reverse(col)) + 2,  -- start: last '.' found from end of string
                     999)                                          -- length: max
                  as last_piece
         , charindex('.', col)  as first_position
         , charindex('.', substring(col, charindex('.', col) + 1, 999)) + charindex('.', col)  as second_position
         , len(col) - charindex('.', reverse(col)) + 1  as last_position
     --      0        1         2
     --      123456789012345678901234567
    (select 'VALUE1.VALUE2.VALUE3.VALUE4'   as col union all
     select 's1.s2.s3.s4'                   as col union all
     select 'un.even.string.lengths!!!'     as col
     ) v

    Open in new window


    Author Closing Comment

    This is a monstrosity... However, it does help with my quest.  I'm leaning on using a function as you suggested.  I thank you for the concept.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Suggested Solutions

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now