Match and Remove Duplicate Values from Delimited String against another Column

Question

I need to match and remove duplicates from a delimited string against another column. Can this be done with Cross Apply?

Example:

TABLE A:

"NAMES","REMOVE"
"James;Kevin;Dave","Kevin"
"Dale;Gordon;Thomas","Dale"
"Anthony;Brian","Brian"

RESULT:

"NEW NAMES","REMOVE"
"James;Dave","Kevin"
"Gordon;Thomas","Dale"
"Anthony","Brian"

As you can see I'm looking for a way to split the values from Names, match on the Remove column, and remove that name into a new field "New Names".

Related reading: [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — GMB, Dec 04 '19 at 22:26

LukStorms · Accepted Answer · 2019-12-05T11:18:41.743

You could simply replace the name.

SELECT *, 
 STUFF(REPLACE(';'+[NAMES], ';'+[REMOVE], ''), 1, 1,'') as [NEW NAMES]
FROM YourTable

But that method can give wrong results in some cases.
For example when removing "Bob" from "John;Bobby;Bob" it would result in "Johnby".

In Sql Server 2017+ a combination of STRING_SPLIT and STRING_AGG is a safer method.

Examples:

create table yourtable
(
   NAMES varchar(100),
   REMOVE varchar(30)
);
GO

✓

INSERT INTO yourtable (NAMES, REMOVE) VALUES
('James;Kevin;Dave', 'Kevin'),
('Dale;Gordon;Thomas', 'Dale'),
('Anthony;Brian', 'Brian'),
('John;Bobby;Bob', 'Bob');
GO

4 rows affected

--
-- Simple replace & stuff
-- WARNING: could give wrong results
--
SELECT t.*
, STUFF(REPLACE(';'+[NAMES],';'+[REMOVE],''),1,1,'') AS [NEW NAMES]
FROM YourTable t;
GO

NAMES              | REMOVE | NEW NAMES    
:----------------- | :----- | :------------
James;Kevin;Dave   | Kevin  | James;Dave   
Dale;Gordon;Thomas | Dale   | Gordon;Thomas
Anthony;Brian      | Brian  | Anthony      
John;Bobby;Bob     | Bob    | Johnby

--
-- Not so simple but safer replace
--
SELECT t.*, 
ISNULL(LEFT(STUFF(REPLACE(';'+[NAMES]+';',';'+[REMOVE]+';',';'),1,1,''), NULLIF(LEN(STUFF(REPLACE(';'+[NAMES]+';',';'+[REMOVE]+';',';'),1,1,'')),0)-1),'') as [NEW NAMES]
FROM YourTable t;
GO

NAMES              | REMOVE | NEW NAMES    
:----------------- | :----- | :------------
James;Kevin;Dave   | Kevin  | James;Dave   
Dale;Gordon;Thomas | Dale   | Gordon;Thomas
Anthony;Brian      | Brian  | Anthony      
John;Bobby;Bob     | Bob    | John;Bobby

--
-- Replace & trim
-- Sql Server 2017+
-- 
SELECT t.*
, TRIM(';' FROM REPLACE(';'+[NAMES]+';', ';'+[REMOVE]+';', ';')) AS [NEW NAMES]
FROM YourTable t
GO

NAMES              | REMOVE | NEW NAMES    
:----------------- | :----- | :------------
James;Kevin;Dave   | Kevin  | James;Dave   
Dale;Gordon;Thomas | Dale   | Gordon;Thomas
Anthony;Brian      | Brian  | Anthony      
John;Bobby;Bob     | Bob    | John;Bobby

--
-- String_split & string_agg
-- Sql Server 2017+
-- 
SELECT t.*, a.[NEW NAMES]
FROM yourtable t
CROSS APPLY
(
  SELECT 
   STRING_AGG(s.value,';') as [NEW NAMES]
  FROM STRING_SPLIT(t.[NAMES],';') s
  WHERE s.value != t.[REMOVE]
) a;
GO

NAMES              | REMOVE | NEW NAMES    
:----------------- | :----- | :------------
James;Kevin;Dave   | Kevin  | James;Dave   
Dale;Gordon;Thomas | Dale   | Gordon;Thomas
Anthony;Brian      | Brian  | Anthony      
John;Bobby;Bob     | Bob    | John;Bobby

Test on db<>fiddle here

Match and Remove Duplicate Values from Delimited String against another Column

1 Answers1