MySQL SELECT 查询字符串匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4916195/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL SELECT query string matching
提问by JR Lawhorne
Normally, when querying a database with SELECT, its common to want to find the records that match a given search string.
通常，当使用 SELECT 查询数据库时，通常希望找到与给定搜索字符串匹配的记录。
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.
该查询应该为我提供名称字段中任何位置出现“Bob Smith”的所有记录。
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.
我不想查找名称字段中包含“Bob Smith”的所有记录，而是要查找名称字段位于“Robert Bob Smith III, PhD.”（查询的字符串参数）中的所有记录。
回答by The Scrum Meister
Just turn the LIKE around
只需转动 LIKE
SELECT * FROM customers
WHERE 'Robert Bob Smith III, PhD.' LIKE CONCAT('%',name,'%')
回答by uncaught_exceptions
You can use regular expressions like this:
您可以像这样使用正则表达式：
SELECT * FROM pet WHERE name REGEXP 'Bob|Smith';
回答by Kushal Raj Gupta
You Can also use
你也可以使用
SELECT * FROM customers WHERE name LIKE "%Bob Smith%";
notice the Double Quotes
注意双引号
回答by Rakesh
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));
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: '_'
输入 v_name 的值：'_'
old 2: where NVL(UPPER(v_name), 1) LIKE nvl (UPPER('%'||&v_name||'%'), NVL(UPPER(v_name), 1))
旧 2: 其中 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))
新 2：其中 NVL(UPPER(v_name), 1) LIKE nvl (UPPER('%'||'_'||'%'), NVL(UPPER(v_name), 1))
V_NAME
V_NAME
Ramu(went gbl)
Ramu（去gbl）
Ramj
拉姆吉
Ramu_Karan(went blr)
Ramu_Karan（去了 blr）
Sidd_Karan
Sidd_Karan
ABC%^
ABC%^
xy123
xy123
abc
美国广播公司
7 rows selected.
已选择 7 行。
output:
输出：
Ramu_Karan(went blr)
Ramu_Karan（去了 blr）
Sidd_Karan
Sidd_Karan
回答by Newtothis
Incorrect:
不正确：
SELECT * FROM customers WHERE name LIKE '%Bob Smith%';
Instead:
反而：
select count(*)
from rearp.customers c
where c.name LIKE '%Bob smith.8%';
select countwill just query (totals)
select count只会查询（总计）
Cwill link the db.table to the names row you need this to index
C将 db.table 链接到您需要索引的名称行
LIKEshould be obvs
LIKE应该是obvs
8will call all references in DB 8 or less (not really needed but i like neatness)
8将调用 DB 8 或更少的所有引用（不是真的需要，但我喜欢整洁）
