We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
11 days, 17 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
compare a value with a semicolon delimeted
Posted on 2016-09-01
I am trying to write query where I am trying to compare a value from one table with a value from second table where the value in the second table is in a row with some other values which is semicolon delimeted
example
table 1 : userid
row 1 prs
row 2 ppp
table2
useridlist
row1 ccc;ddd;eee;PRS;rrr
row2 qqq;www;ttt
so i need a query that takes value for row 1 ('prs') and compares it with useridlist of table 2 and since there is a match in row1 of table2 do not show it. then it takes row1 of table 1 and compares with useridlist of table2 and since there no match it shows.
example
table 1 : userid
row 1 prs
row 2 ppp
table2
useridlist
row1 ccc;ddd;eee;PRS;rrr
row2 qqq;www;ttt
so i need a query that takes value for row 1 ('prs') and compares it with useridlist of table 2 and since there is a match in row1 of table2 do not show it. then it takes row1 of table 1 and compares with useridlist of table2 and since there no match it shows.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
2
4 Comments
Active 4 days ago
check this.
select *
from table1 t1
left join table2 t2 on patindex('%'+t1.userid+'%',t2.useridlist) > 0
where t2.useridlist is null
Active 5 days ago
Use any of the below approaches
Solution 1
--------------------------
SELECT Words.Texts Word , r.Texts Texts
FROM Words
CROSS APPLY
(
SELECT Texts FROM Texts
)r
WHERE CHARINDEX(Words.Texts,r.Te
SOLUTION 2
--------------------------
SELECT Words.Texts Word , r.Texts Texts
FROM Words
CROSS JOIN Texts r
WHERE CHARINDEX(Words.Texts,r.Te
URL for ref --
https://msbiskills.com/2016/07/06/sql-puzzle-word-search-puzzle/
Enjoy ! Let me know in case you want me to write the exact query.. for that you need to share your table design for both the tables.
Solution 1
--------------------------
SELECT Words.Texts Word , r.Texts Texts
FROM Words
CROSS APPLY
(
SELECT Texts FROM Texts
)r
WHERE CHARINDEX(Words.Texts,r.Te
SOLUTION 2
--------------------------
SELECT Words.Texts Word , r.Texts Texts
FROM Words
CROSS JOIN Texts r
WHERE CHARINDEX(Words.Texts,r.Te
URL for ref --
https://msbiskills.com/2016/07/06/sql-puzzle-word-search-puzzle/
Enjoy ! Let me know in case you want me to write the exact query.. for that you need to share your table design for both the tables.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Why is this different from all of the other step by step guides? Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month11 days, 17 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.