SQL Delete sentence in one string that exists in another

Question

I am using Microsoft SQL Server 2016.

I have two strings containing email addresses called @Recipients and @Copy_Recipients.

I need to cut out those emails from @Copy_Recipients that exists in @Recipients.

@Recipients = 'john_snow@com.eu;daenerys_stormborn@com.eu;tonny_hawk@com.eu';

@Copy_Recipients = 'john_snow@com.eu;daenerys_stormborn@com.eu;tommy_leejones@com.eu;johny_cash@com.eu;';

@Wanted_Result = 'tommy_leejones@com.eu;johny_cash@com.eu;';

I know I am not bringing any idea of how to fix this. That's why I am writing. To ask what methods or functions could I use to achieve my goal. Thank you.

You need to tag the exact RDBMS (e.g. SQL Server, MySQL, Postgres) you're using because each could have its own approach to the problem. — MarcinJ, Feb 24 '22 at 11:20
Hint : [STRING_SPLIT](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15) `@Copy_Recipients`, check for Not [EXISTS](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-ver15) in `@Recipients`, [STRING_AGG](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15) the result to form new `@Copy_Recipients ` — Squirrel, Feb 24 '22 at 11:37

MarcinJ · Accepted Answer · 2022-02-24T12:37:14.597

2

If you're on SQL Server 2017 or later, you can use STRING_SPLIT and STRING_AGG

SELECT STRING_AGG(copy.value, ';')
  FROM STRING_SPLIT(@Copy_Recipients, ';') copy
  LEFT OUTER
  JOIN STRING_SPLIT(@Recipients, ';') recipients
    ON recipients.value = copy.value
 WHERE recipients.value IS NULL

Working demo on dbfiddle

If you're on earlier versions of SQL Server, you'll have to use workarounds for the functions used above.

If you're on 2016, you can do it slightly differently:

DECLARE @Result NVARCHAR(MAX) = @Copy_Recipients

SELECT @Result = REPLACE(@Result, value, '') FROM STRING_SPLIT(@Recipients, ';')

And the final result with semicolons trimmed:

SELECT SUBSTRING(@Result, PATINDEX('%[^;]%', @Result), LEN(@Result)) AS FinalResult

edited Feb 24 '22 at 12:37

answered Feb 24 '22 at 11:39

MarcinJ

3,471
2
14
18

unfortunately I am using SQL Server 2016. ```STRING_AGG``` isn't available for me. What would you suggest instead? – Simonsoft177 Feb 24 '22 at 11:45
I updated my answer with a way to do it in 2016, you'll have to deal with left-over ; at the beginning/end of the recipient list, should be easy enough. – MarcinJ Feb 24 '22 at 11:50
Actually it's as easy as TRIM(';' FROM @Result), updated again. – MarcinJ Feb 24 '22 at 11:52
```Incorrect syntax near the keyword 'FROM'``` I tried declaring @Result before but it seem to have problem with this. – Simonsoft177 Feb 24 '22 at 12:15
Oh my, `TRIM( ... FROM ...) ` is also only 2019! I updated the answer with a solution that will work on 2016 as per the demo fiddle. – MarcinJ Feb 24 '22 at 12:38
1

@Simonsoft177, as an FYI, using the syntax `SELECT @Variable = @Variable + ... FROM` is a [documented antipattern](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-ver15#c-antipattern-use-of-recursive-variable-assignment) and should be avoided. Instead use string aggregation to achieve the same results. In recent versions of SQL Server that would be by using `STRING_AGG`, however, in older versions you'll need to use `FOR XML PATH` (and `STUFF`) to achieve the same results. – Thom A Feb 24 '22 at 12:52

SQL Delete sentence in one string that exists in another

1 Answers1