SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE FUNCTION dbo.SplitStrings(@List nvarchar(max))
RETURNS TABLE
AS
   RETURN ( SELECT Item FROM
       ( SELECT Item = x.i.value(N'./text()[1]', N'nvarchar(max)')
         FROM ( SELECT [XML] = CONVERT(xml, '<i>'
         + REPLACE(@List,',', '</i><i>') + '</i>').query('.')
           ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
       WHERE Item IS NOT NULL
   );
DECLARE @foo TABLE(ID int IDENTITY(1,1), col nvarchar(max));


INSERT @foo(col) SELECT N'c,d,e,f,g';
INSERT @foo(col) SELECT N'c,e,b';
INSERT @foo(col) SELECT N'd,e,f,x,a,e';

DECLARE @string NVARCHAR(MAX) = N'a,b,c,d';

;WITH x AS
(
    SELECT f.ID, c.Item FROM @foo AS f
    CROSS APPLY dbo.SplitStrings(f.col) AS c
), y AS
(
    SELECT ID, Item FROM x
    UNION
    SELECT x.ID, s.Item
        FROM dbo.SplitStrings(@string) AS s
        CROSS JOIN x
)
SELECT ID, Items = STUFF((SELECT ',' + Item 
    FROM y AS y2 WHERE y2.ID = y.ID 
    FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'')
FROM y
GROUP BY ID;


ID
Items


1
a,b,c,d,e,f,g

2
a,b,c,d,e

3
a,b,c,d,e,f,x


DECLARE @foo TABLE(ID int IDENTITY(1,1), col nvarchar(max));

INSERT @foo(col) SELECT N'c,d,e,f,g';
INSERT @foo(col) SELECT N'c,e,b';
INSERT @foo(col) SELECT N'd,e,f,x,a,e';

DECLARE @string NVARCHAR(MAX) = N'a,b,c,d';

    ;WITH x AS
    (
        SELECT f.ID, c.value FROM @foo AS f
        CROSS APPLY STRING_SPLIT
        (
          CONCAT(f.col, N',', @string), N','
        ) AS c GROUP BY f.ID, c.value
    )
    SELECT ID, STRING_AGG(value, N',')
    WITHIN GROUP (ORDER BY value)
    FROM x GROUP BY ID;


ID
(No column name)


1
a,b,c,d,e,f,g

2
a,b,c,d,e

3
a,b,c,d,e,f,x