是否可以在 SQL 的 LIKE 语句中执行不区分大小写的搜索？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9931626/
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
Is it possible to perform a case insensitive search in LIKE statement in SQL?
提问by aman.kejriwal
I am using to write a select query and the value for like statement is dynamic.
我正在编写一个选择查询，并且 like 语句的值是动态的。
AND e.rank_request_id = a.request_id
AND f.priority_request_id = a.request_id
AND b.status_type_id = c.status_id
AND b.status_request_id = a.request_id
AND a.request_id LIKE '%#form.searchbar#%'
But this returns results only where Case of each character in the string #form.searchbar# is matched.
但这仅返回匹配字符串 #form.searchbar# 中每个字符的大小写的结果。
Please suggest a workaround for this so that it becomes case-insensitive.
请为此提出一个解决方法，使其不区分大小写。
回答by Snipe656
I do not know what database you are using but if this were for Oracle then you could just force the case of both things. This though comes at a cost for execution times since it does it for all values in that column but you'd only see the cost if you have a lot of data and could work around that with a function based index. So something like this, again for Oracle:
我不知道您使用的是什么数据库，但如果这是针对 Oracle 的，那么您可以强制使用两种情况。这虽然会增加执行时间的成本，因为它会针对该列中的所有值执行此操作，但只有在您拥有大量数据并且可以使用基于函数的索引解决此问题时，您才会看到成本。所以像这样的事情，对于甲骨文来说：
AND UPPER(a.request_id) LIKE '%#UCase(Form.Searchbar)#%'
But I would suggest you use a queryparam since appears to come from a user inputted box, so:
但我建议您使用 queryparam，因为它似乎来自用户输入的框，因此：
AND UPPER(a.request_id) LIKE <cfqueryparam value="%#UCase(Form.Searchbar)#%" cfsqltype="cf_sql_varchar" />
回答by Soader03
You could lower a.request_id and form.searchbar
你可以降低 a.request_id 和 form.searchbar
AND lower(a.request_id) LIKE '%#form.searchbar#%'
回答by Tim Lehner
回答by Justin Cave
You can force everything to uppercase as Snipe656 suggests. You can also use the regexp_instrfunction to do a case-insensitive search. For example, to search the EMPtable for every row where ENAMEcontains the string 'in' in a case-insensitive fashion
您可以按照 Snipe656 的建议将所有内容强制为大写。您还可以使用该regexp_instr函数进行不区分大小写的搜索。例如，以不区分大小写的方式在EMP表中搜索ENAME包含字符串 'in' 的每一行
SQL> ed
Wrote file afiedt.buf
1 select ename, empno
2 from emp
3* where regexp_instr( ename, 'in', 1, 1, 1, 'i' ) > 0
SQL> /
ENAME EMPNO
---------- ----------
MARTIN 7654
KING 7839
In your case, it would probably be something like
在你的情况下，它可能是这样的
AND regexp_instr( a.request_id, '#form.searchbar#', 1, 1, 1, 'i' ) > 0
