0

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.

GSerg
  • 76,472
  • 17
  • 159
  • 346
Milacay
  • 1,407
  • 7
  • 32
  • 56
  • 1
    The *real* problem here is that you're storing delimited values (and passing a delimited valued parameter too). None of those are really numbers, they are strings. You really need to normalise your data and then this question would be trivial. – Thom A Aug 20 '19 at 17:19
  • 1
    I agree with Larnu, the way that the data is stored is not ideal for sql to be able to do this. While keeping the current schema though there are at least a couple of options being to use code to make the changes (Make a python script for example that can pull in the data and make the updates) or you can create a cursor that would curse down the replace numbers (may have to change them to a table type rather than a string) and delete any occurrence of them. – dmoore1181 Aug 20 '19 at 17:25
  • 1
    If you must use this data, I suggest looking up `STRING_SPLIT` or `delimitedsplit8k_LEAD` along with `STRING_AGG` or `FOR XML PATH` for string aggregations. The real answer is fixing your data though. – Thom A Aug 20 '19 at 17:29
  • Thank you for your suggestion. I must use this data and will try best to see if I can figure this out. – Milacay Aug 20 '19 at 17:37
  • 1
    It's worth mentioning that STRING_SPLIT / STRING_AGG were added in 2016. If you're on an older version, this becomes difficult. – OwlsSleeping Aug 20 '19 at 18:11
  • 1
    @OwlsSleeping string_agg was added in 2017, but string_split in 2016. – Zohar Peled Aug 20 '19 at 18:25
  • Thank you for the STRING_SPLIT / STRING_AGG (I just learned it). It must easier than using User Defined Function to split them. – Milacay Aug 20 '19 at 18:26

1 Answers1

2

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

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121