John Pietrar John Pietrar - 4 months ago 9
SQL Question

Read each string that follows a char in sql

So let us say you have a string:

set @string = 'aaa,2,dqw,3,asdad,5,4'


I would like to read the chars that are after a char and a ","
So the result to this string would be:

Result
--------
2
3
5


How could I do this?is there a way to use
CHARINDEX
for this?

Answer

If your string is just like your example, using Charindex(',', <string>) works too. Otherwise, use PATINDEX. It functions similarly,but you can also set it to recognize all numeric characters.

IF PATINDEX('%[0-9]%', @String') <> 0
    THEN BEGIN
             SET @string = SUBSTRING(@string, PATINDEX('%[0-9]%', @string), LEN(@string) )
             SET @var = SUBSTRING(@string, PATINDEX('%[^0-9]%', @string) )
             END

you now can do as you please with those variables.

To your exception, just use one more case statement and rid of it. No reason the code has to be too complicated.

Comments