Extract a part of the string from comma separated string in SQL Server

Question

I have a varchar string in SQL Server:

DECLARE @vStr VARCHAR(MAX) = ';ABC,DEF,+123456789,+987654321,0,0,0,0,0,+987654321,1110:'

For this string I want result like:

'+123456789,+987654321,0,0,0,0,0,+987654321'

DECLARE @vStr VARCHAR(MAX) = ';PQR,XYZ,+987654321,+987654321,0,0,0,0,0,+9876123456,5555:'

should generate result like:+987654321,+987654321,0,0,0,0,0,+9876123456

I am trying substring with left and len but it is not working.

UPDATE:

In general I do not want first two part of this comma separated string and also I do not want last part of the comma separated string.

do you have mix alpha + numeric case ? like ABC123, DEF123 ? — Squirrel
– Squirrel, Commented Mar 15, 2016 at 6:25
What's the general rule? From the first string with + up to the last string with +? — Felix Pamittan
– Felix Pamittan, Commented Mar 15, 2016 at 6:28
@FelixPamittan General rule is I do not want first two values from coma separated string. For first string I do not want ";ABC" and "Def,". and I do not want last portion. i.e. 1110 in first example. I hope I have made my self clear — Anand Shah
– Anand Shah, Commented Mar 15, 2016 at 6:32

Squirrel · Accepted Answer · 2016-03-15 06:53:42Z

3

select  result = stuff(item, 1, charindex(',', item, charindex(',', item) + 1), '')
from
(
    -- exclude last item
    select  item = left(@vStr, len(@vStr) - charindex(',', reverse(@vStr)))
) d

explaination

the inner query is to exlude the last item. This is done by using reversing the string (reverse()) and then find the comma "," and then use LEFT() to get the left part up to the last comma
the 2 charindex() on the outer query is to find the 2nd comma
And then remove whatever up the 2nd comma using stuff()

edited Mar 15, 2016 at 6:53

answered Mar 15, 2016 at 6:42

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Felix Pamittan Over a year ago

Brilliant solution! Would be better if you include some more explanation.

Anand Shah Over a year ago

@Squirrel Thanks for this timely solution

yuvi · Accepted Answer · 2016-03-15 06:28:44Z

0

Can you Please try this

select substring( substring (substring(@vStr,1,len(@vStr)),10,len(@vStr)),0,len(@vStr)-14 )

answered Mar 15, 2016 at 6:28

yuvi

5745 silver badges12 bronze badges

1 Comment

Anand Shah Over a year ago

Your answer works for the both examples I have given above. But I have plenty of strings which I need to parse. See the general expected result I have updated in question. Your this solution does not work with Select @vStr = ';1GALQR,20111,19727328849,0,0,0,0,0,0,0,583424:'

Collectives™ on Stack Overflow

Extract a part of the string from comma separated string in SQL Server

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related