compare a value with a semicolon delimeted

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.
PratikShah111Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
SharathConnect With a Mentor Data EngineerCommented:
check this.
select *
  from table1 t1
  left join table2 t2 on patindex('%'+t1.userid+'%',t2.useridlist) > 0
 where t2.useridlist is null

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
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.Texts,0) > 0

SOLUTION 2

-------------------------------------

SELECT Words.Texts Word , r.Texts Texts
FROM Words
CROSS JOIN Texts r
WHERE CHARINDEX(Words.Texts,r.Texts,0) > 0


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.
0
 
Pawan KumarDatabase ExpertCommented:
@Author - Do you need more help with this ?
0
 
PratikShah111Author Commented:
thank you Sharath for your help. exactly what I wanted.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.