[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1669
  • Last Modified:

CHARINDEX Nth positions parsing.

I'm parsing a column of text which separates the data by "." - VALUE1.VALUE2.VALUE3.VALUE4

I can get VALUE1 with SELECT SUBSTRING(COLUMN, 1, CHARINDEX('.', COLUMN + '.') - 1)
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,
-czlong
0
czlong
Asked:
czlong
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
try this logic

create function fn_ParseCSVString
(
@CSVString       varchar(8000) ,
@Delimiter      varchar(10)
)
returns @tbl table (s varchar(1000))
as
/*
select * from dbo.fn_ParseCSVString ('qwe,c,rew,c,wer', ',c,')
*/
begin
declare @i int ,
      @j int
      select       @i = 1
      while @i <= len(@CSVString)
      begin
            select      @j = charindex(@Delimiter, @CSVString, @i)
            if @j = 0
            begin
                  select      @j = len(@CSVString) + 1
            end
            insert      @tbl select substring(@CSVString, @i, @j - @i)
            select      @i = @j + len(@Delimiter)
      end
      return
end
0
 
awking00Commented:
0
 
alpmoonCommented:
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)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
czlongAuthor Commented:
@alpmoon, I tried that before I posted and that option isn't available to me.  That would have made this light work.
0
 
czlongAuthor Commented:
@awking00, Same this as @alpmoon suggestion. My CHARINDEX only takes 2 args.
0
 
lwadwellCommented:
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
from 
 --      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

0
 
czlongAuthor Commented:
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now