MySQL SELECT query string matching

Question

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.

Have to agree with @Pekka. Perhaps if you supply a few example sets of input data you're expecting matches for and the relevant search term(s). — John Parker
– John Parker, Commented Feb 6, 2011 at 21:21

The Scrum Meister · Accepted Answer · 2011-02-06 21:24:26Z

51

Just turn the LIKE around

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

answered Feb 6, 2011 at 21:24

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

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

4 Comments

JR Lawhorne Over a year ago

Exactly it. I tried this but didn't do it with CONCAT on the end. Works like a charm. Thx.

Landon Kuhn Over a year ago

Will this result in a full table scan? Is there a solution that uses an index?

The Scrum Meister Over a year ago

@landon9720 Yes. The only index that can possibly be used is a FULLTEXT index

srj Over a year ago

in case you are using sqlite, you can concat the strings with '||' operator SELECT * FROM customers WHERE 'Robert Bob Smith III, PhD.' LIKE '%' || name || '%' ;

skytreader · Accepted Answer · 2015-01-27 11:06:08Z

12

You can use regular expressions like this:

SELECT * FROM pet WHERE name REGEXP 'Bob|Smith';

edited Jan 27, 2015 at 11:06

skytreader

11.8k7 gold badges46 silver badges63 bronze badges

answered Feb 6, 2011 at 21:25

billygoat

22.1k5 gold badges43 silver badges50 bronze badges

1 Comment

user2060451 Over a year ago

I am using REGXP on a 200,000 row DB, and it does not bring a whole bunch...

ADyson · Accepted Answer · 2016-10-03 09:58:57Z

-6

Incorrect:

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

Instead:

select count(*)
from rearp.customers c
where c.name  LIKE '%Bob smith.8%';

select count will just query (totals)

C will link the db.table to the names row you need this to index

LIKE should be obvs

8 will call all references in DB 8 or less (not really needed but i like neatness)

edited Oct 3, 2016 at 9:58

ADyson

62.9k16 gold badges85 silver badges99 bronze badges

answered Oct 2, 2016 at 16:49

Newtothis

12 bronze badges

1 Comment

Andres Ramos Over a year ago

It seems like you didn't understand the original question.

Collectives™ on Stack Overflow

MySQL SELECT query string matching

3 Answers 3

4 Comments

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Linked

Related