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

```
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.