SQL 如何在忽略大小写的sql中比较字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/871578/
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
How to compare strings in sql ignoring case?
提问by Gary.Ray
How do I write a query in Oracle ignoring the case of the strings being compared? For example "angel", "Angel", "ANGEL", "angel", "AngEl" would all be equal when compared.
如何在 Oracle 中编写查询而忽略正在比较的字符串的大小写？例如，“angel”、“Angel”、“ANGEL”、“angel”、“AngEl”在比较时都是相等的。
回答by Gary.Ray
If you are matching the full value of the field use
如果您匹配该字段的完整值，请使用
WHERE UPPER(fieldName) = 'ANGEL'
EDIT: From your comment you want to use:
编辑：根据您要使用的评论：
SELECT
RPAD(a.name, 10,'=') "Nombre del Cliente"
, RPAD(b.name, 12,'*') "Nombre del Consumidor"
FROM
s_customer a,
s_region b
WHERE
a.region_id = b.id
AND UPPER(a.name) LIKE '%SPORT%'
回答by bendewey
回答by Pablo Santa Cruz
You can use:
您可以使用：
select * from your_table where upper(your_column) like '%ANGEL%'
Otherwise, you can use:
否则，您可以使用：
select * from your_table where upper(your_column) = 'ANGEL'
Which will be more efficient if you are looking for a match with no additional characters before or after your_columnfield as Gary Ray suggested in his comments.
如果您正在寻找在your_column字段之前或之后没有附加字符的匹配项，这将更有效，正如 Gary Ray 在他的评论中所建议的那样。
回答by Smart003
before comparing the two or more strings first execute the following commands
在比较两个或多个字符串之前先执行以下命令
alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT=BINARY_CI;
after those two statements executed then you may compare the strings and there will be case insensitive.for example you had two strings s1='Apple' and s2='apple', if yow want to compare the two strings before executing the above statements then those two strings will be treated as two different strings but when you compare the strings after the execution of the two alter statements then those two strings s1 and s2 will be treated as the same string
执行完这两个语句之后，您可以比较字符串，并且不区分大小写。例如，您有两个字符串 s1='Apple' 和 s2='apple'，如果您想在执行上述语句之前比较这两个字符串，则这两个字符串将被视为两个不同的字符串，但是当您在执行两个 alter 语句后比较字符串时，这两个字符串 s1 和 s2 将被视为相同的字符串
reasons for using those two statements
使用这两个语句的原因
We need to set NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_CI in order to use 10gR2 case insensitivity. Since these are session modifiable, it is not as simple as setting them in the initialization parameters. We can set them in the initialization parameters but they then only affect the server and not the client side.
我们需要设置 NLS_COMP=LINGUISTIC 和 NLS_SORT=BINARY_CI 以使用 10gR2 不区分大小写。由于这些是会话可修改的，因此并不像在初始化参数中设置它们那么简单。我们可以在初始化参数中设置它们，但它们只会影响服务器而不是客户端。
回答by Gary Myers
More detail on Mr Dredel's answer and tuinstoel's comment. The data in the column will be stored in its specific case, but you can change your session's case-sensitivity for matching.
关于 Dredel 先生的回答和 tuinstoel 评论的更多细节。列中的数据将以其特定的大小写存储，但您可以更改会话的大小写敏感度以进行匹配。
You can change either the session or the database to use linguistic or case insensitive searching. You can also set up indexes to use particular sort orders.
您可以更改会话或数据库以使用语言或不区分大小写的搜索。您还可以设置索引以使用特定的排序顺序。
eg
例如
ALTER SESSION SET NLS_SORT=BINARY_CI;
Once you start getting into non-english languages, with accents and so on, there's additional support for accent-insensitive. Some of the capabilities vary by version, so check out the Globablization document for your particular version of Oracle. The latest (11g) is here
一旦您开始使用带有重音等的非英语语言，就会额外支持重音不敏感。某些功能因版本而异，因此请查看针对您的特定 Oracle 版本的全局化文档。最新的 (11g) 在这里
回答by Yevgeny Simkin
I don't recall the exact syntax, but you may set the table column to be case insensitive. But be careful because then you won't be able to match based on case anymore and if you WANT 'cool' to not match 'CoOl' it will no longer be possible.
我不记得确切的语法，但您可以将表列设置为不区分大小写。但要小心，因为那样你将无法再根据大小写进行匹配，如果你想“酷”而不匹配“酷”，那将不再可能。
