SQL: checking if row contains contents of another cell

Question

I'm having difficulty with a SQL query.

I have two tables:

Table 1

ID/First Name
1    Ben
2    Barry
3    Birl

Table 2

ID/Full name 
1     Ben Rurth
2     Barry Bird
3     Burney Saf

I want to run a check between the two tables where if the contents of the First Name in Table 1 is not in the Full name in table 2 the result will be returned, e.g. returning id 3, Birl, in the above example.

I have been trying queries like:

SELECT First_Name 
from Table_1 
WHERE NOT EXIST (SELECT Full_name from Table_2)

with no luck so far.

Do you want a query for SQLite, or for some other DB? – CL. Oct 26 '16 at 10:16 — CL., Oct 26 '16 at 10:16

score 1 · Answer 1 · answered Oct 26 '16 at 09:59

1

You can make use of LIKE clause combined with concatenation.

SELECT t1.First_Name,t2.Full_Name
FROM Table1 t1
JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.Full_Name NOT LIKE '%' || t1.First_Name || '%'

Or

SELECT t1.First_Name,t2.Full_Name
FROM Table1 t1
JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.Full_Name NOT LIKE CONCAT('%', t1.First_Name, '%')

This is, understanding that both tables shares the ID column.

answered Oct 26 '16 at 09:59

antorqs

619
3
18

I do not work with SQL Server but as far as I know, yes it does https://msdn.microsoft.com/en-us/library/hh231515.aspx @AT-2016 – antorqs Oct 26 '16 at 10:03

SQL: checking if row contains contents of another cell

1 Answers1