Oracle 中使用 LIKE 的重音和不区分大小写的排序规则

Accent and case insensitive collation in Oracle with LIKE

提问by Ed .

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.

我发现这个答案很有用： Oracle 中的重音和不区分大小写的 COLLATE 等效，但我的问题是关于使用版本 9 Oracle db 进行 LIKE 搜索。

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')

but no results are ever returned.

但永远不会返回任何结果。

I created a little Java file to test:

我创建了一个小 Java 文件来测试：

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);
  }
 }
}

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.

我想对名称的一部分执行搜索并返回使用不区分大小写和重音的匹配结果。

回答by Vincent Malgrat

one method would be to modify your session parameters NLS_SORTand NLS_COMP:

一种方法是修改您的会话参数NLS_SORT和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

As shown in another SO, you cannot use the LIKE operator with NLSSORT(this is because, NLSSORT returns a string of bytesthat will be used for sorting, and LIKE only works with charater strings)

如另一个 SO所示，您不能使用 LIKE 运算符NLSSORT（这是因为，NLSSORT 返回将用于排序的字节字符串，而 LIKE 仅适用于字符字符串）

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:

更新：虽然设置 NLS 参数是我的第一选择，但您也可以使用内置函数来实现相同的结果。几个例子：

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, 'àaéèêì?ò?ù??', 'aaeeeiioouuy'))
  4         LIKE upper(translate('%pe%', 'àaéèêì?ò?ù??', 'aaeeeiioouuy'));

NAME
-----------------------------------
pepe
pépé
PEPE