Click here to Skip to main content
14,978,528 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hey,
Got two fields with multiple values.
Field A            Field B       Field C      Field D      
Apple,Pear,Banana  Apple, Pear


In filed C i would like to have missing value from field, in Field D missig value from Field B.

What I have tried:

New to sql and would like to learn
Posted
Updated 25-May-20 3:43am
v2
Comments
OriginalGriff 25-May-20 9:03am
   
This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with. So show us sample inputs - more than one, please, the outputs you expect, what you have tried so far, and explain what happened when you tried it.

Use the "Improve question" widget to edit your question and provide better information.
Maciej Los 25-May-20 9:44am
   
Define, what is: "missing value".
F-ES Sitecore 25-May-20 12:10pm
   
If this is how you are storing your data then I would start with a redesign and hold your data in parent\child relationship tables. Not only will it make solving the problem you are trying to solve easier, it will stop you running into all sorts of problems you are guaranteed to run into when you store data that way.

1 solution

If you are using SQL Server 2016 or higher, you could use the STRING_SPLIT function:
STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^]

If you are using SQL Server 2017 or higher, you could use the STRING_AGG function to join strings: STRING_AGG (Transact-SQL) - SQL Server | Microsoft Docs[^]

The query below finds the missing value in field b 'Banana'
CREATE table test (a varchar(20), b varchar(20), c varchar(20), d varchar(20));
INSERT INTO test (a,b,c,d) VALUES ('Apple,Pear,Banana', 'Apple,Pear', '', '');

SELECT value FROM test
CROSS APPLY STRING_SPLIT(a, ',')
WHERE value NOT IN (SELECT value FROM STRING_SPLIT(b, ','));
   
v3
Comments
Maciej Los 25-May-20 9:44am
   
OP is looking for a way to display missing values in another columns.
RickZeeland 25-May-20 9:52am
   
Yes, the answer is far from complete I know, the problem is that I don't even have SQL Server installed anymore as we are using PostgreSQL now, so maybe anyone with SQL Server installed can answer this better (assuming the OP uses SQL Server) ...

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



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