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.
CodePudding user response:
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
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 TRIM(';' FROM @Result)