TSQL take right value from each comma separated value

Question

12344-23s2$4

I have a value that I receive from the feed

12344-23s2$4, 12344-23s2$5, 12344-23s2$6, 12344-23s2$7

all the above values are separated by comma.

For each comma separated value I need to take only the right part that is after - (hyphen)

I tried using the below statement

right(ID, len(ID) - CHARINDEX('-', ID))

If I have only one value I get the correct value.. i.e., 23s2$4. but if i have multiple comma separated value i will get all the values after first -.

I want to take each comma separated value and from that each value I want to take only the right part(after hyphen)

How do you import this? And is the first bit always "12344"? — gbn, Aug 15 '11 at 15:59

score 1 · Answer 1 · answered Aug 15 '11 at 16:04

So you've solved the problem for a single value -- that's a good step. Now, make a list of single values. One way is to turn it into a table. Here's a function I've used, that turns a comma-delimited string into a table:

CREATE Function [dbo].[ParseStringList]  (@StringArray nvarchar(max) )  
Returns @tbl_string Table  (ParsedString nvarchar(max))  As  

BEGIN 

DECLARE @end Int,
        @start Int

SET @stringArray =  @StringArray + ',' 
SET @start=1
SET @end=1

WHILE @end<Len(@StringArray)
    BEGIN
        SET @end = CharIndex(',', @StringArray, @end)
        INSERT INTO @tbl_string 
            SELECT
                Substring(@StringArray, @start, @end-@start)

        SET @start=@end+1
        SET @end = @end+1
    END

RETURN
END

Then you select from the table like this:

Select ParsedString From dbo.ParseStringList(@StringArray)

So from here, you can apply your solution:

Select right(ParsedString, len(ParsedString) - CHARINDEX('-', ParsedString))
From dbo.ParseStringList(@StringArray)

score 1 · Answer 2 · edited May 23 '17 at 12:01

If you import this using SQLBulkCopy, BULK INSERT or bcp then you can split the CSV into separate columns at import time. Then your SQL works per column.

If you can't do this, then split (see how here: Split function equivalent in T-SQL?) the value into rows.

Now you have separated the CSV (as rows or columns) use either

your RIGHT code above
if the leading value is always 12344, then use REPLACE (MyValue, '12344-', '')

score 1 · Accepted Answer · answered Aug 15 '11 at 16:06

1

You are going to need to do this within a stored proc. In the stored proc, you will have to create a while loop and loop through the field until you have processed all of your commas.

answered Aug 15 '11 at 16:06

Adam

497
2
9
29

No need to loop: the value can be split quite simply – gbn Aug 15 '11 at 16:09

score 1 · Answer 4 · answered Aug 15 '11 at 16:08

Typically when you try to do something like this you have to create a UDF that allows you to split a string based on a delimiter. This article goes over a fairly good one:

http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

You can also look at using some XML functions to do this:

Declare @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='12344-23s2$4, 12344-23s2$5, 12344-23s2$6, 12344-23s2$7'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
SELECT right(N.value('.', 'varchar(16)'), len(N.value('.', 'varchar(16)')) - CHARINDEX('-', N.value('.', 'varchar(16)'))) as value FROM @xml.nodes('X') as T(N)

TSQL take right value from each comma separated value

4 Answers4