SQL Server – Remove some numbers in a string contains numbers

Question

I have a column contains string with numbers formatted as comma delimiter. I want to write an update query to update those numbers in the column based on the numbers to be removed that provided, but I ...

Accepted Answer

I have a column contains string with numbers formatted as comma delimiter. I want to write an update query to update those numbers in the column based on the numbers to be removed that provided, but I am stuck. I know it is confusing, so here is the explanation:

Says, I have the query:

Select Column1 FROM tblTest Where RecID = 1

Result of Column1:

8,7,11,13,15,9,10,12,16,14,5,6,4,3,21,19,18,17,1,2,20

If I have this variable below (@Str_RemoveNumbers) contains numbers to be removed, the update query somehow can update the Column1 with those numbers removed.

Declare @Str_RemoveNumbers Varchar(MAX)
SET @Str_RemoveNumbers = '8,10,20,1'
--  .... And Update Query Here....

Then the value for Column1 will update to this after query executed:

7,11,13,15,9,12,16,14,5,6,4,3,21,19,18,17,2

Please help, Thanks in advance.

Answer

I have a column contains string with numbers formatted as comma delimiter.

There’s your problem.

The solution would be to normalize your data.
For more information, read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!

Having said that, based on your comments you can’t change the database structure, your only viable choice is to do it the hard way.

First, you need to split the string stored in Column1 to a table. Then, you need to split the string in @Str_RemoveNumbers to another table. Then you need to aggregate the result of a query taking data from one table that doesn’t exist in the other back to a comma delimited string, and store that back into Column1.

Assuming you are working with 2017 or a higher version, You can use the built in functions of string_split and string_agg so at least some of the heavy lifting is done for you.

If you are working with 2016 version, the string_split is available to you but string_agg is not, you will need to replace it with another well-known solution based on stuff and for xml. You can find an example for this here. (and in many other places.)

If you are working with an older version, you need to split your strings using older techniques – pick a string splitting function from Aaron Bertrand’s Split strings the right way – or the next best way.

Having said all that, here’s an 2017 version based workaround (I refuse to call this mess a solution!) for your misfortune:

First, create and populate sample table (Please save us this step in your future questions):

CREATE TABLE tblTest
(
    RecID int,
    Column1 varchar(100)
);

INSERT INTO tblTest(RecId, Column1) VALUES
(1, '1,2,3,4,5'),
(2, '6,7,8,9,10');

Then, declare and populate the variables:

DECLARE @RecId int = 1,
        @Str_RemoveNumbers Varchar(MAX) = '2, 4, 20';

Now here’s the “Fun” part – using a couple of common table expressions and an update statement:

-- This cte splits the column1 value to a table
WITH CTE1 AS
(
    SELECT RecID, TRIM([Value]) As [Value]
    FROM tblTest 
    CROSS APPLY STRING_SPLIT(Column1, ',') 
    WHERE RecID = @RecId
)
-- This cte returns values from column1 that doesn't exist in @Str_RemoveNumbers
, CTE2 AS
(
SELECT RecID, STRING_AGG([Value], ',') As Col1
FROM CTE1
WHERE [Value] NOT IN(
    SELECT TRIM([Value]) 
    FROM STRING_SPLIT(@Str_RemoveNumbers, ',')
)
GROUP BY RecID
)

-- this updates the table with the results of cte2
UPDATE t
SET Column1 = Col1
FROM tblTest As t
JOIN CTE2
ON t.RecId = CTE2.RecId

Verify:

SELECT *
FROM tblTest

Results:

RecID   Column1
1       1,3,5
2       6,7,8,9,10

DB<>Fiddle

Advertisement

Answer