I'd suggest to use CTE[^]. See:

SQL

DECLARE @tmp TABLE(CurrentValue NVARCHAR(30), MasterListofvalue NVARCHAR(150)) INSERT INTO @tmp (CurrentValue, MasterListofvalue) VALUES('A', 'Aa;B;C;D'), ('B', 'A;B;C;D'), ('C', 'A;B;C;D') ;WITH CTE AS ( --initial query SELECT CurrentValue, MasterListofvalue, LEFT(MasterListofvalue, CHARINDEX(';', MasterListofvalue)-1) AS PartOfMasterList, RIGHT(MasterListofvalue, LEN(MasterListofvalue) - CHARINDEX(';', MasterListofvalue)) AS Remainder FROM @tmp WHERE CHARINDEX(';', MasterListofvalue)>0 --recursive part UNION ALL SELECT CurrentValue, MasterListofvalue, LEFT(Remainder, CHARINDEX(';', Remainder)-1) AS PartOfMasterList, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(';', Remainder)) AS Remainder FROM CTE WHERE CHARINDEX(';', Remainder)>0 UNION ALL SELECT CurrentValue, MasterListofvalue, Remainder AS PartOfMasterList, NULL AS Remainder FROM CTE WHERE CHARINDEX(';', Remainder)=0 ) SELECT CurrentValue, MasterListofvalue, COALESCE(MAX(ExactMatch), 'N/A') AS ExactMatch, MAX(SimilarMatch) AS SimilarMatch FROM ( SELECT t.*, CASE WHEN c.MasterListofvalue = t.CurrentValue THEN t.CurrentValue ELSE NULL END AS ExactMatch, CASE WHEN c.PartOfMasterList = t.CurrentValue THEN t.CurrentValue ELSE NULL END AS SimilarMatch, c.PartOfMasterList FROM @tmp as t LEFT JOIN CTE AS c ON c.CurrentValue = t.CurrentValue ) AS Final GROUP BY CurrentValue, MasterListofvalue ORDER BY CurrentValue

Result:

CurrentValue MasterListofvalue ExactMatch SimilarMatch A Aa;B;C;D N/A NULL B A;B;C;D N/A B C A;B;C;D N/A C

Try explaining your problem better - for example, I don't understand why B and C have a "similar match" but A does not. State the rules clearly.

B and C is similar match because they are one of the values in the MasterListof Values column. With respect to A the MasterListValue consists of Aa hence it is not a perfect match, but it should be under Similar match and the value over there should be A.

Here is the function :-

CREATE FUNCTION [dbo].[SplitString]

(

@List NVARCHAR(MAX),

@Delimiter NVARCHAR(255)

)

RETURNS TABLE

WITH SCHEMABINDING AS

RETURN

WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),

E2(N) AS (SELECT 1 FROM E1 a, E1 b),

E4(N) AS (SELECT 1 FROM E2 a, E2 b),

E42(N) AS (SELECT 1 FROM E4 a, E2 b),

cteTally(N) AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1)))

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),

cteStart(N1) AS (SELECT t.N+1 FROM cteTally t

WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))

SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))

FROM cteStart s;

GO

cross apply with the data table I would be getting the split values of Msater column one below the other to get the exact match.