How to check if a column contains a substring of string in SQL?

Question

I want to check if a column in my database contains words similar to my sample string. The opposite of

select * from myTable where name like '%words%';

so that if I have record with name=word I could retrieve it. With sample above I can only get result where words is a sub string of name column in myTable, therefore I cant get word

JNevill · Accepted Answer · 2021-12-06 17:31:23Z

4

You just flip the two terms in your LIKE operator:

SELECT * 
FROM mytable
WHERE 'words' LIKE CONCAT('%',name,'%')

I believe that LOCATE() and INSTR() may work here too which looks nicer since there isn't a need for concatenating the search term/substring.

SELECT *
FROM mytable
WHERE INSTR('words', name) > 0

edited Dec 6, 2021 at 17:31

answered Dec 6, 2021 at 17:25

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

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

2 Comments

Farnaz Daneshvar Over a year ago

Thanks a lot. How is this CONCAT('%',name,'%') working? Is there a link you could forward me to?

JNevill Over a year ago

It's merely tossing the wild cards around each name and searching that. It's exactly the same as your example but instead of saying Where this "name" column is like this string we are saying Where this string is like this name column. Not sure if there is documentation since it's just the LIKE operator doing its thing.

Collectives™ on Stack Overflow

How to check if a column contains a substring of string in SQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related