[SOLVED] SQL Delete sentence in one string that exists in another

SQL Delete sentence in one string that exists in another

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.

Solution

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