36

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.

JR Lawhorne
  • 3,192
  • 4
  • 31
  • 41

3 Answers3

51

Just turn the LIKE around

SELECT * FROM customers
WHERE 'Robert Bob Smith III, PhD.' LIKE CONCAT('%',name,'%')
The Scrum Meister
  • 29,681
  • 8
  • 66
  • 64
  • Exactly it. I tried this but didn't do it with CONCAT on the end. Works like a charm. Thx. – JR Lawhorne Feb 06 '11 at 21:58
  • 1
    Will this result in a full table scan? Is there a solution that uses an index? – Landon Kuhn Aug 01 '12 at 14:42
  • @landon9720 Yes. The only index that can possibly be used is a [`FULLTEXT` index](http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html) – The Scrum Meister Aug 01 '12 at 15:03
  • 1
    in case you are using sqlite, you can concat the strings with '||' operator `SELECT * FROM customers WHERE 'Robert Bob Smith III, PhD.' LIKE '%' || name || '%' ;` – srj Sep 02 '14 at 18:23
12

You can use regular expressions like this:

SELECT * FROM pet WHERE name REGEXP 'Bob|Smith';
skytreader
  • 11,467
  • 7
  • 43
  • 61
uncaught_exceptions
  • 21,712
  • 4
  • 41
  • 48
-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)

ADyson
  • 57,178
  • 14
  • 51
  • 63
Newtothis
  • 1
  • 2