如何在 Oracle SQL 中对结果进行不区分大小写的排序？
从 Java 不区分大小写地查询 SQL 数据库的经典方法如下：
String name = ...; // get the user's input (case is unknown)
String sql = "select * from Person where lower(name) = ?";
Object jdbcBindVariable = name.toLowerCase();
// ... using JDBC, bind that variable and run the SQL query
问题是小写是特定于区域设置的操作。例如，小写字母“I”在英语和土耳其语中会产生不同的结果。在上面的代码中，有两个小写操作：
- String#toLowerCase() 方法
- lower() 数据库函数
如何确保 Java 和数据库使用相同的语言环境，从而执行有效的比较？
我知道 String 类有一个 toLowerCase(Locale) 方法，但我如何知道数据库正在使用什么区域设置？我可以通过编程方式检查这一点，还是必须将区域设置硬编码为我认为配置数据库（在本例中为 Oracle 10g）的区域设置？
The classic way to query an SQL database case-insensitively from Java is as follows:
String name = ...; // get the user's input (case is unknown)
String sql = "select * from Person where lower(name) = ?";
Object jdbcBindVariable = name.toLowerCase();
// ... using JDBC, bind that variable and run the SQL query
The problem is that lower-casing is a locale-specific operation. For example, lower-casing the letter "I" gives different results in English and Turkish. In the above code, there are two lower-casing operations:
- The String#toLowerCase() method
- The lower() database function
How can I make sure that Java and the database are using the same locale and thereby performing a valid comparison?
I'm aware that the String class has a toLowerCase(Locale) method, but how do I know what Locale the database is using? Can I check this programatically, or do I have to hard-code the locale to the one with which I think the database (in this case Oracle 10g) is configured?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
简单的答案是让数据库来做。
这样，绑定变量的小写方式将与列值的小写方式一致。
The simple answer is let the database do it.
That way the way the bind variable is put into lower case will be consistent with the way the column value is put into lowercase.
似乎没有一种可移植（独立于数据库）的方法来执行此操作，但您显然可以使用以下查询来获取 Orable 数据库使用的字符集：
此页面提供更多详细信息。
至于实际进行比较，您最好 (*) 让数据库处理小写，正如@Gary 建议的那样。 JDBC 驱动程序将负责将 Java (UTF-16) 字符串转换为数据库正在使用的任何字符串。
（* 事实上，我认为您没有太多选择，除非您准备承担在数据库中存储所有可查询字符串的混合大小写和小写副本的成本。）
There doesn't appear to be a portable (database independent) way to do this, but you can apparently use the following query to get the charset used by an Orable database:
This page gives more details.
As for actually doing the comparison, you would be best off (*) letting the database take care of the lower-casing, as @Gary suggests. The JDBC driver will take care of converting Java (UTF-16) Strings into whatever the database is using.
(* In fact, I don't think you have much choice, unless you are prepared to wear the cost of storing mixed-case and lower-case copies of all queriable strings in the database.)