CODEWITHSUNDEEP
mysqlsql
user2113896
user2113896

Reputation: 131

sql Check if column is substring of another column

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.

Upvotes: 13

Views: 33243

Answers (3)

Melvin Varughese
Melvin Varughese

Reputation: 26

LOCATE is not available in some SQL flavours (e.g. Postgres) and apparently using string concatenation to build a SQL query is frowned upon (source: TypeError: 'dict' object does not support indexing).

You could alternatively use the POSITION command:

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

Upvotes: 0

Barmar
Barmar

Reputation: 782508

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

Upvotes: 18

sgeddes
sgeddes

Reputation: 62861

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 '%'.

Upvotes: 4

Related Questions