sql Check if column is substring of another column

Question

I am trying to check one column substring is in another columns

table1

FullName              
Johns Doue               
Johnny                   
Betty Smith, Chair     

table2

Name       
John

using table2 to see if it is a substring of table1. it should return Johns Doue and Johnny.

SELECT * FROM table1.FullName AS table1      
JOIN table2.Name AS table2    
WHERE table2.Name LIKE SUBSTRING(table1.FullName, 0, 10);

this is returning null's being compared. im not sure what i am doing wrong. From my logic, it seems like its taking the results from table2.name and comparing to the substrings of table1.FullName.

Answer 1

You need to put wildcards in the LIKE pattern to make it look for substrings. You also seem to be confusing table and column names in your SELECT syntax.

SELECT *
FROM table1 AS t1
JOIN table2 AS t2
WHERE t1.FullName LIKE CONCAT('%', t2.Name, '%')

You can also use LOCATE instead of LIKE

SELECT *
FROM table1 AS t1
JOIN table2 AS t2
WHERE LOCATE(t2.Name, t1.FullName) > 0

DEMO

Answer 2

Here's one option using a join with like and concat:

select * 
from table1 as t1
    join table2 as t2 on t1.FullName like concat('%',t2.name,'%')

If you only want full names that start with the name, then remove the first '%'.

• SQL Fiddle Demo