TSQL take right value from each comma separated value

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 va开发者_如何学运维lue 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)

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.

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)

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

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)