Sql query to compare single values in a column to the delimeted string

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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 11-Feb-20 20:02pm

chints786

Updated 12-Feb-20 0:49am

Maciej Los

v2

Add a Solution

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2020-02-12T00:49:00

If i understand you correctly...

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

Sayee Subramania Sastry · Answer 2 · 2020-02-11T23:27:00

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 +"%"

Posted 11-Feb-20 23:27pm

Sayee Subramania Sastry

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