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
5 days, 3 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 today
check this.
select *
from table1 t1
left join table2 t2 on patindex('%'+t1.userid+'%',t2.useridlist) > 0
where t2.useridlist is null
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
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| encrypt SQL Server 2008 port 1433 | 3 | 48 | |
| SQL - Creating Pivot with all the possible columns | 7 | 33 | |
| T-SQL: Trying to use a "NOT IN (Subquery)" in CASE Statement | 2 | 30 | |
| SSIS Package Not Running in Batch File | 3 | 10 |
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Course of the Month5 days, 3 hours left to enroll
739 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.