CODEWITHSUNDEEP
sql-serverstored-procedures
Bill
Bill

Reputation: 1477

Remove a value from a comma separated string in sql stored procedure

I have a field that store the comma separated id's of publications. Such as 123456,2345678,123567,2345890 etc. When I pull the data from the database I put it into a json object and the web page loops the values and displays the data. Works great.

What I would like to do is to send the stored proc one of the numbers and the stored proc will remove it from the string and save it back to the table. Such as the end user worked on publication 123567 and now wants to make it completed, so I want to remove it from the string so they don't see it in the future. I have a split function in the database but I don't know how to wire it up to delete or rebuild the string without the publication ID.

I don't have any code to show because I am at a loss to start. I figure I need to pass the entire string and the ID. Split the string and loop each value to rebuild a new string but check if the ID is there and skip it.

Is this the best way to do this?

Thanks for your help

what I've ended up with as the base to work from is:

ALTER FUNCTION dbo.RemovePMID (
    @S VARCHAR(MAX)
    ,@PMID VARCHAR(15)
    )
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @T VARCHAR(50)
    DECLARE @W VARCHAR(50)

    SET @T = ''

    WHILE len(@S) > 0
    BEGIN
        SET @W = left(@S, charindex(',', @S + ',') - 1)

        IF charindex(@W, + @PMID) = 0
            SET @T = @T + ',' + @W
        SET @S = stuff(@S, 1, charindex(',', @S + ','), '')
    END

    RETURN substring(@T, 2, len(@T) - 2)
END
GO

Upvotes: 2

Views: 3112

Answers (2)

dev101
dev101

Reputation: 1

It will remove a value from string

Declare @S varchar(max) = '22,22,16,12'

Declare @Zap varchar(50)='22'

Select  STUFF((
                                SELECT ',' + s.value
                                FROM STRING_SPLIT(@S, ',') s
                                WHERE s.value NOT IN (SELECT @Zap)
                                FOR XML PATH('')), 1, 1, '');

which returns

16,12

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

No need for loops (please take a peek at Larnu's suggestion for your parse/split function)

That said, consider the following

Example

Declare @S varchar(max) = '123456,2345678,123567,2345890'

Declare @Zap varchar(50)='123456'

Select reverse(stuff(reverse(stuff(replace(','+@S+',',','+@Zap+',',','),1,1,'')),1,1,''))

Returns

2345678,123567,2345890

Upvotes: 4

Related Questions