在Oracle中使用LIKE进行重音和不区分大小写的排序规则 [英] Accent and case insensitive collation in Oracle with LIKE
问题描述
我发现此答案很有用:
口音和
I have found this answer useful: Accent and case insensitive COLLATE equivalent in Oracle, but my question is regarding LIKE searching with a version 9 Oracle db.
我已经尝试过类似这样的查询:
I have tried a query like this:
SELECT column_name FROM table_name WHERE NLSSORT(column_name, 'NLS_SORT = Latin_AI') LIKE NLSSORT('%somethingInDB%', 'NLS_SORT = Latin_AI')
但不会返回任何结果。
我创建了一个小的Java文件来测试:
I created a little Java file to test:
import org.apache.commons.dbcp.BasicDataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class DbCollationTest
{
public static void main(String[] args) throws SQLException
{
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
dataSource.setUrl("url");
dataSource.setUsername("usr");
dataSource.setPassword("pass");
Connection conn = null;
PreparedStatement createStatement = null;
PreparedStatement populateStatement = null;
PreparedStatement queryStatement = null;
PreparedStatement deleteStatement = null;
ResultSet rs = null;
try
{
conn = dataSource.getConnection();
createStatement = conn.prepareStatement("CREATE TABLE CollationTestTable ( Name varchar(255) )");
createStatement.execute();
String[] names = { "pepe", "pépé", "PEPE", "MEME", "mémé", "meme" };
int i = 1;
for (String name : names)
{
populateStatement = conn.prepareStatement("INSERT INTO CollationTestTable VALUES (?)");
populateStatement.setString(1, name);
populateStatement.execute();
}
queryStatement = conn.prepareStatement("SELECT Name FROM CollationTestTable WHERE NLSSORT(NAME, 'NLS_SORT = Latin_AI') LIKE NLSSORT('%pe%', 'NLS_SORT = Latin_AI')");
rs = queryStatement.executeQuery();
while (rs.next())
{
System.out.println(rs.getString(1));
}
deleteStatement = conn.prepareStatement("DROP TABLE CollationTestTable");
deleteStatement.execute();
}
finally
{
//DBTools.tidyUp(conn, null, rs);
//DBTools.tidyUp(createStatement);
//DBTools.tidyUp(populateStatement);
//DBTools.tidyUp(queryStatement);
//DBTools.tidyUp(deleteStatement);
}
}
}
googling,any people have any solutions?
I have not had any success googling, anyone have any solutions?
我想对名称的一部分执行搜索,并返回使用大小写和重音不敏感匹配的结果。
I want to perform a search on part of a name and return results that are matched using case and accent insensitivity.
提前感谢。
推荐答案
一种方法是修改会话参数 NLS_SORT
和 NLS_COMP
:
one method would be to modify your session parameters NLS_SORT
and NLS_COMP
:
SQL> SELECT Name FROM CollationTestTable WHERE NAME LIKE '%pe%';
NAME
--------------------------------------------------------------------------------
pepe
SQL> alter session set nls_sort=Latin_AI;
Session altered
SQL> alter session set nls_comp=linguistic;
Session altered
SQL> SELECT Name FROM CollationTestTable WHERE NAME LIKE '%pe%';
NAME
--------------------------------------------------------------------------------
pepe
pépé
PEPE
如另一个SO ,你不能使用LIKE运算符 NLSSORT
(这是因为, NLSSORT返回一个字符串,将会是用于排序,LIKE仅适用于charater字符串)
As shown in another SO, you cannot use the LIKE operator with NLSSORT
(this is because, NLSSORT returns a string of bytes that will be used for sorting, and LIKE only works with charater strings)
更新:设置NLS参数将是我的首选,使用内置函数来实现相同的结果。几个示例:
Update: While setting the NLS parameters would be my first choice, you could also use built-in functions to achieve the same result. A couple of examples:
SQL> SELECT Name
2 FROM CollationTestTable
3 WHERE upper(convert(NAME, 'US7ASCII'))
4 LIKE upper(convert('%pe%', 'US7ASCII'));
NAME
--------------------------------------------------------------------------------
pepe
pépé
PEPE
SQL> SELECT Name
2 FROM CollationTestTable
3 WHERE upper(translate(NAME, 'àâéèêìîòôùûÿ', 'aaeeeiioouuy'))
4 LIKE upper(translate('%pe%', 'àâéèêìîòôùûÿ', 'aaeeeiioouuy'));
NAME
-----------------------------------
pepe
pépé
PEPE
这篇关于在Oracle中使用LIKE进行重音和不区分大小写的排序规则的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!