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.

Barmar · Accepted Answer · 2016-07-06 23:38:28Z

19

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

edited Jul 6, 2016 at 23:38

answered Jul 6, 2016 at 22:25

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2113896 Over a year ago

i did the first query but the t2.name resulting a null

Barmar Over a year ago

I added a demo showing that both queries work with your sample data.

sgeddes · Accepted Answer · 2016-07-06 22:24:04Z

4

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

answered Jul 6, 2016 at 22:24

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

6 Comments

user2113896 Over a year ago

I have already done that but its still giving me a results of an empty string thats compared to another empty string.

user2113896 Over a year ago

or there is something populated in the t1.fullname but its being compared with t2.name and t2.name is null. fullname john doue name (empty)

sgeddes Over a year ago

@user2113896 -- guessing here a little, but what about just adding where criteria to eliminate blank values? sqlfiddle.com/#!9/290973/2

user2113896 Over a year ago

yeah i added a statement where it checks if its not null but doesnt work.

sgeddes Over a year ago

@user2113896 -- at this point, you need to clarify your question... did you try the working fiddle? can you replicate the issue you are having in it and repost the link to the one that doesn't work correctly?

|

Melvin Varughese · Accepted Answer · 2019-12-17 01:25:27Z

0

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

answered Dec 17, 2019 at 1:25

Melvin Varughese

263 bronze badges

Collectives™ on Stack Overflow

sql Check if column is substring of another column

3 Answers 3

2 Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related