SELECT from 3rd comma in string

问题:

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.

回答1:

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

回答2:

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.

回答3:

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

回答4:

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))