Removing "1, " and "2, " from a string with out removing "11, " and "12, "

Question

I have a column of strings that can contain 1-15 in a comma separated list (e.g. "1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15" or "2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15" or "1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15")

How do I remove the "1, " and the "2, " from all examples above?

already tried:

Replace(Replace(@code, "1, ", "")@code, "2, ", "")
Trim(@code)

Initially was thinking to use the replace function but doing so also removes the "1, " and "2, " from "11, " and "12, ".

What are other techniques that can be used here?

Answer 1

As I mention in the comments the real solution is to fix your design.

Let's create a quick sample dataset:

CREATE TABLE dbo.YourTable(ID int IDENTITY(1,1) CONSTRAINT PK_YourTable PRIMARY KEY,
                           SomeInt int NOT NULL,
                           SomeDate date NULL,
                           YourDenormalisedColumn varchar(8000) NOT NULL);
GO

INSERT INTO dbo.YourTable (SomeInt,
                           SomeDate,
                           YourDenormalisedColumn)
VALUES(24,GETDATE(),'1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15'),
      (117,NULL,'2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15'),
      (3,'20231117','1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15');

Creating and INSERTing the data is actually quite simple, we just CREATE the minimal detail of the table we need and then use STRING_SPLIT to insert the new data. Then we can DROP the old denormalised column:

CREATE TABLE dbo.NormalisedData (ArbitraryID int IDENTITY CONSTRAINT PK_NormalisedData PRIMARY KEY,
                                 YourID int NOT NULL CONSTRAINT FK_NormalisedData_YourTable_YourID FOREIGN KEY REFERENCES dbo.YourTable(ID),
                                 NormalisedValue int NOT NULL);
GO

INSERT INTO dbo.NormalisedData (YourID,
                                NormalisedValue)
SELECT YT.ID,
       SS.value
FROM dbo.YourTable YT
     CROSS APPLY STRING_SPLIT(YT.YourDenormalisedColumn,',') SS;
GO

ALTER TABLE dbo.YourTable DROP COLUMN YourDenormalisedColumn;

Now we have a normalised dataset your requirement is easy, it's just a simple DELETE:

DELETE FROM dbo.NormalisedData
WHERE NormalisedValue IN (1,2);

---

If you are adamant you can't change your design (you can, should, and must), then you could split the string, remove the "offending" values, and then re-aggregate:

--Run before the COLUMN is DROPed
UPDATE YT
SET YT.YourDenormalisedColumn = (SELECT STRING_AGG(SS.value,', ') WITHIN GROUP (ORDER BY ss.ordinal)
                                 FROM STRING_SPLIT(YT.YourDenormalisedColumn,',',1) SS
                                 WHERE TRIM(ss.value) NOT IN ('1','2')) --TRIM because you have leading spaces and ' 1' <> '1'
FROM dbo.YourTable YT;

---

--Clean up
DROP TABLE dbo.NormalisedData;
DROP TABLE dbo.YourTable;

db<>fiddle

Answer 2

The correct thing to do would be to stop using delimited strings.

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!

However I know sometimes changing the database structure is impossible even if it is the correct thing to do - so a workaround would be to include the delimiter in the first argument of the replace and then trim it back out.

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

CREATE TABLE TableName (
  Id int identity(1,1) ,
  DelimitedValues varchar(100)
);

INSERT INTO TableName (DelimitedValues) VALUES
('1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15'), 
('2, 3, 1, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15'), 
('1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15');

The update statement:

UPDATE TableName
SET DelimitedValues = TRIM(', ' FROM 
  REPLACE(', '+ DelimitedValues +', ', ', 1, ', ', '))
WHERE Id < 3;

A quick select to make sure the update was successful:

SELECT Id, DelimitedValues
FROM TableName

Results:

Id	DelimitedValues
1	2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15
2	2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15
3	1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15

You can see a live demo on db<>fiddle