With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf
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 2 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 today
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
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses
Course of the Month10 days, 6 hours left to enroll
722 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.