Helpex - Trao đổi & giúp đỡ Đăng nhập
7

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.

7 hữu ích 0 bình luận 9.2k xem chia sẻ
7

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

7 hữu ích 2 bình luận chia sẻ
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 '%'.

4 hữu ích 5 bình luận chia sẻ
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
0 hữu ích 0 bình luận chia sẻ
loading
Không tìm thấy câu trả lời bạn tìm kiếm? Duyệt qua các câu hỏi được gắn thẻ mysql sql , hoặc hỏi câu hỏi của bạn.

Có thể bạn quan tâm

loading