14,739,420 members
See more:
Hi All,

I have 2 columns.

Current value
MasterListOfValues

MasterListOfValues is '`;`' delimited.eg of values:- `A;B;C;D`

The table looks like this:-

```current value  MasterListofvalue
A              Aa;B;C;D
B              A;B;C;D
C              A;B;C;D```

Now for every match we should make another column say Exact Match and put that value there , and if the exact match is not there we should create another column similar match.

The output should look like below:-

```current value  MasterListofvalue  Exact Match  Similar Match
A              Aa;B;C;D              N/A
B              A;B;C;D                          B
C              A;B;C;D                          C```

What I have tried:

I had tried creating a split function but Able to get the desired result.
Posted
Updated 12-Feb-20 1:49am
v2
CHill60 12-Feb-20 3:18am

You are meant to show us the code that didn't work. Plus, your explanation is not very clear. You should also avoid storing delimited values in a column - it's bad design.
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.
chints786 12-Feb-20 5:13am

Hi,

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.

## Solution 2

If i understand you correctly...

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```
chints786 12-Feb-20 9:17am

Thanks Maciej Los, however there is a gap in this.

When the current value is A we will need to check each value in the corresponding MasterList of values and check if we get the actual 'A' in it. In this case we wont get because it has 'Aa' in it. So the exact match is false or NA.

However when we traverse for Similar match kind of like clause 'A' will match with 'Aa' and hence similar match must have 'A' in it i.e. Partial match.

Similarly for the other values.

Hope you understand.
Maciej Los 12-Feb-20 9:30am

Have you tried my solution. Seems, you don't.
Take a look at result returned by my code. As you see, there's no exact match for any current value. There's no even similar match for 'Aa'.
chints786 12-Feb-20 9:41am

I did try it,and that is the point I am trying to understand.

A when matched to 'Aa,B,C' must give 'N/A' in exact match column and only 'A' in the similar match column.

Another example would be

'Brush' when matched to 'Brush On';'Bru On' then the exact match would be 'N/A' but the Similar match would be 'Brush'.

Once you get a exact match of the current value we won't need to find a similar match for it.
Maciej Los 12-Feb-20 10:00am

Well...
The output returned by CTE is exactly the same as an output designed by you. If you want to change something, feel free.
Note, that CTE splits MasterListofvalue values into parts on [`;`]. So, the query returned by CTE is used with original data to get common parts.
```...
FROM @tmp as t
LEFT JOIN CTE AS c ON c.CurrentValue = t.CurrentValue
...```

This is the place where the magic is getting into real.

## Solution 1

```The following select statement will give you the result, Similarly you can use the update statements to get desired columns

select * from test where MasterListofvalue > "%"+current_value +"%"```
CHill60 12-Feb-20 7:57am

That won't work. It would match on 'Aa' for 'A' which the OP has said is incorrect