Search code examples
sqlsql-servert-sqlmailing

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 = '[email protected];[email protected];[email protected]';

@Copy_Recipients = '[email protected];[email protected];[email protected];[email protected];';

@Wanted_Result = '[email protected];[email protected];';

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