Click here to Skip to main content
14,691,272 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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


Please advise on this.

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

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
   
Comments
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.
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 +"%"
   
Comments
CHill60 12-Feb-20 7:57am
   
That won't work. It would match on 'Aa' for 'A' which the OP has said is incorrect

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900