MySQL SELECT query string matching

问题:

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.

回答1:

Just turn the LIKE around

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

回答2:

You can use regular expressions like this:

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

回答3:

You Can also use

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

notice the Double Quotes

回答4:

In the above query If we are searching with some special character for example: '__' or '()' than its retrieving all data,

SQL> select * from test_brck where NVL(UPPER(v_name), 1) LIKE nvl (UPPER('%'||&v_name||'%'), NVL(UPPER(v_name), 1));

Enter value for v_name: '_'

old 2: where NVL(UPPER(v_name), 1) LIKE nvl (UPPER('%'||&v_name||'%'), NVL(UPPER(v_name), 1))

new 2: where NVL(UPPER(v_name), 1) LIKE nvl (UPPER('%'||'_'||'%'), NVL(UPPER(v_name), 1))

V_NAME

Ramu(went gbl)

Ramj

Ramu_Karan(went blr)

Sidd_Karan

ABC%^

xy123

abc

7 rows selected.

output:

Ramu_Karan(went blr)

Sidd_Karan

回答5:

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)