MySQL SELECT query string matching

sql mysql string match
JR Lawhorne picture JR Lawhorne · Feb 6, 2011 · Viewed 112.6k times · Source

Normally, when querying a database with SELECT, its common to want to find the records that match a given search string.

For example:

SELECT * FROM customers WHERE name LIKE '%Bob Smith%';

That query should give me all records where 'Bob Smith' appears anywhere in the name field.

What I'd like to do is the opposite.

Instead of finding all the records that have 'Bob Smith' in the name field, I want to find all the records where the name field is in 'Robert Bob Smith III, PhD.', a string argument to the query.

Answer

The Scrum Meister picture The Scrum Meister · Feb 6, 2011

Just turn the LIKE around

SELECT * FROM customers
WHERE 'Robert Bob Smith III, PhD.' LIKE CONCAT('%',name,'%')

Related questions

How to use GROUP BY to concatenate strings in MySQL?

Basically the question is how to get from this: foo_id foo_name 1 A 1 B 2 C to this: foo_id foo_name 1 A B 2 C

Read More
SQL order string as number

I have numbers saved as VARCHAR to a MySQL database. I can not make them INT due to some other depending circumstances. It is taking them as character not as number while sorting. In database I have 1 2 3 4 5 6 7 8 9 10... On my page …

Read More
How can I append a string to an existing field in MySQL?

I want to update the code on all my record to what they currently are plus _standard any ideas? So for example if the codes are apple_1 and apple_2 I need them to be apple_1_standard and apple_2_standard Before: …

Read More