CODEWITHSUNDEEP
sql-server
Stpete111
Stpete111

Reputation: 3457

Remove string portion from inconsistent string of comma-separated values

SQL Server 2017 on Azure.

Given a field called Categories in a table called dbo.sources: 

ID   Categories
1    ABC01, FFG02, ERERE, CC201
2    GDF01, ABC01, GREER, DS223
3    DSF12, GREER
4    ABC01
5    NULL

What is the syntax for a query that would remove ABC01 from any record where it exists, but keep the other codes in the string?

Results would be:

ID   Categories
1    AFFG02, ERERE, CC201
2    GDF01, GREER, DS223
3    DSF12, GREER
4    NULL
5    NULL

Upvotes: 0

Views: 35

Answers (2)

Thom A
Thom A

Reputation: 96016

Normalising and then denormalising your data, you can do this:

USE Sandbox;
GO

CREATE TABLE dbo.Sources (ID int,
                          Categories varchar(MAX));
INSERT INTO dbo.Sources
VALUES (1,'ABC01,FFG02,ERERE,CC201'), --I **assume you don't really have the space)
       (2,'GDF01,ABC01,GREER,DS223'),
       (3,'DSF12,GREER'),
       (4,'ABC01'),
       (5,NULL);
GO

DECLARE @Source varchar(5) = 'ABC01'; --Value to remove

WITH CTE AS(
    SELECT S.ID,
           STRING_AGG(NULLIF(SS.[value],@Source),',') WITHIN GROUP(ORDER BY S.ID) AS Categories
    FROM dbo.Sources S
         CROSS APPLY STRING_SPLIT(S.Categories,',') SS
    GROUP BY S.ID)
UPDATE S
SET Categories = C.Categories
FROM dbo.Sources S
     JOIN CTE C ON S.ID = C.ID;
GO
SELECT ID,
       Categories
FROM dbo.Sources

GO

DROP TABLE dbo.Sources;

Although this seems like a bit overkill, compared to the REPLACE, it shows why normalising it is a far better idea in the first place, and how simple it is to actually do so.

Upvotes: 2

Kemal AL GAZZAH
Kemal AL GAZZAH

Reputation: 1047

You can use Replace as follows:

update dbo.sources set
    category = replace(replace(category,'ABC01',''),', ','')
where category like '%ABC01%'

Upvotes: 1

Related Questions