SELECT from 3rd comma in string

Question

I have the following string:

bzip2,1,668,sometext,foo,bar

How can I SELECT only sometext,foo,bar? The length of the string preceding the 3rd comma may differ and there may be commas within sometext,foo,bar.

I'd like this in as concise code as possible, i.e. preferably 1 line of code, no loops. But feel free to post any solution you think of.

Answer 1

I just figured out something that works:

declare @v varchar(max) = 'bzip2,1,668,sometext'
select substring(@v, CHARINDEX(',', @v, CHARINDEX(',', @v, CHARINDEX(',', @v)+1)+1)+1, len(@v))

Answer 2

try this:

Do a substring from 3rd comma to the end of the string.. To find the 3 commas , I am using charindex() function 3 times

  declare @str varchar(50)='bzip2,1,668,some,text'

  select substring(@str,
  CHARINDEX(',',@str,CHARINDEX(',',@str,CHARINDEX(',',@str,1)+1)+1)+1,
  LEN(@str)-CHARINDEX(',',@str,CHARINDEX(',',@str,CHARINDEX(',',@str,1)+1)+1))

result:

some,text

Answer 3

Code:

declare @input varchar(max) = 'bzip2,1,668,s,o,m,e,t,e,x,t,f,o,o,b,a,r'
--declare @input varchar(max) = 'bzip2,,'
declare @thirdCommaPosition int = nullif(charindex(',', @input, nullif(charindex(',', @input, nullif(charindex(',', @input, 1),0)+1),0)+1 ),0)
select stuff(@input, 1, @thirdCommaPosition, '')

Output:

s,o,m,e,t,e,x,t,f,o,o,b,a,r

Edit

Added nullif's to the third comma calculation part, without them it's possible to get inconsistent results.

Answer 4

Here is another idea

DECLARE @xml AS XML,@str AS VARCHAR(50)
    SET @str='bzip2,1,668,sometext,foo,bar'

    SET @xml = CAST(('<X>'+REPLACE(@str,',' ,'</X><X>')+'</X>') AS XML)


        SELECT FinalResult = STUFF(@str,1,SUM(Length)+3,' ' ) FROM (SELECT 
                                Rn = ROW_NUMBER() OVER(ORDER BY (SELECT 1)) 
                                ,N.value('.', 'varchar(10)') as value
                                ,Length = LEN(N.value('.', 'varchar(10)'))  
                            FROM @xml.nodes('X') as T(N))X 
        WHERE X.Rn<=3

Result

 sometext,foo,bar