oracle 查找索引大小写,关于sql：Oracle中不区分大小写的搜索

LIKE和其他比较运算符=等的默认行为区分大小写。

有可能让他们不区分大小写吗？

友好提示：即使用户名上有索引，一些示例搜索也会导致完整的表扫描。

你考虑过用REGEXP_LIKE(username,'me','i')而不是like吗？

不，我觉得可以

在不使用全文索引的情况下，在Oracle中执行不区分大小写的搜索有三种主要方法。

最终，您选择的方法取决于您的个人情况；要记住的一点是，为了提高性能，您必须正确索引以进行不区分大小写的搜索。1。将列和字符串大小写相同。

您可以使用UPPER()或LOWER()强制所有数据为同一情况：

SELECT * FROM my_table WHERE UPPER(column_1) = UPPER('my_string');

或

SELECT * FROM my_table WHERE LOWER(column_1) = LOWER('my_string');

如果column_1没有在upper(column_1)或lower(column_1)上建立索引(视情况而定)，这可能会强制进行全表扫描。为了避免这种情况，您可以创建一个基于函数的索引。

CREATE INDEX my_index ON my_table ( LOWER(column_1) );

如果使用like，则必须将%连接到要搜索的字符串周围。

SELECT * FROM my_table WHERE LOWER(column_1) LIKE LOWER('my_string') || '%';

这个SQL助手演示了在所有这些查询中会发生什么。请注意解释计划，它指示何时使用索引，何时不使用索引。2。使用正则表达式。

从Oracle 10g起，提供REGEXP_LIKE()。为了执行不区分大小写的搜索，可以指定"匹配"参数"EDOCX1"(12)。

为了将其用作相等运算符，必须指定字符串的开始和结束，该字符串由克拉和美元符号表示。

SELECT * FROM my_table WHERE regexp_like(column_1, '^my_string$', 'i');

为了执行类似的操作，可以删除这些内容。

SELECT * FROM my_table WHERE regexp_like(column_1, 'my_string', 'i');

注意这一点，因为字符串可能包含正则表达式引擎将以不同方式解释的字符。

除了使用regexp ou like()之外，此sql fiddle向您显示相同的示例输出。三。在会话级别更改它。

nls-sort参数控制排序的排序顺序和各种比较运算符，包括=等。可以通过更改会话来指定二进制、不区分大小写的排序。这意味着在该会话中执行的每个查询都将执行不区分大小写的参数。

ALTER SESSION SET nls_sort=BINARY_CI

如果你想指定一种不同的语言，或者使用二进制搜索来进行不区分重音的搜索，那么关于语言排序和字符串搜索还有很多额外的信息。

您还需要更改nls_comp参数；以引用：

The exact operators and query clauses that obey the NLS_SORT parameter

depend on the value of the NLS_COMP parameter. If an operator or

clause does not obey the NLS_SORT value, as determined by NLS_COMP,

the collation used is BINARY.

nls_comp的默认值是二进制的；但是，language指定Oracle应注意nls_sort的值：

Comparisons for all SQL operations in the WHERE clause and in PL/SQL

blocks should use the linguistic sort specified in the NLS_SORT

parameter. To improve the performance, you can also define a

linguistic index on the column for which you want linguistic

comparisons.

因此，再一次，您需要更改会话

ALTER SESSION SET nls_comp=LINGUISTIC

如文档中所述，您可能希望创建一个语言索引来提高性能

CREATE INDEX my_linguistc_index ON my_table

(NLSSORT(column_1, 'NLS_SORT = BINARY_CI'));

"创建一个基于函数的索引"这能带来多大的不同

我能问一下为什么用select * from my_table where lower(column_1) LIKE lower('my_string') || '%';代替select * from my_table where lower(column_1) LIKE lower('my_string%');是不同的吗？它有什么好处吗？

一个原因是，如果查询是参数化的(可能在大多数情况下)，那么调用代码不需要总是在末尾@lopezvit连接一个%。

如果有一些字符会破坏regexp_like的结果，有没有一种方法可以转义这样的字符串？举个例子，如果字符串有$的话，输出将不会像我们期望的那样。//cc@ben等请分享。

`是bozzmob的转义符。如果正则表达式所操作的字符串包含一个$，那么输出应该没有区别，这只会在正则表达式中需要一个$字面值时引起问题。如果你有一个具体的问题，我会问另一个问题，如果这个评论/回答没有帮助。

sqlfiddle似乎不起作用：(

我想用第一种方式增加更多。比如说，像我是Bob这样的记录存在，那么它不会像在开始时搜索关键字Bob那样工作，也不会返回该值。因此，要在记录中任何可以使用的位置搜索关键字，请从"我的"表中选择"*"，其中较低的(列)如"%"较低的("我的"字符串")"%"；。

此时，您将永远不会使用indexes@yashwin。根据数据量的不同，您可能需要考虑其他选项，例如全文索引。

自10GR2以来，Oracle允许通过设置NLS_COMP和NLS_SORT会话参数来微调字符串比较的行为：

SQL> SET HEADING OFF

SQL> SELECT *

2  FROM NLS_SESSION_PARAMETERS

3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT

BINARY

NLS_COMP

BINARY

SQL>

SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH

2  FROM DUAL;

0

SQL>

SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

SESSION altered.

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

SESSION altered.

SQL>

SQL> SELECT *

2  FROM NLS_SESSION_PARAMETERS

3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT

BINARY_CI

NLS_COMP

LINGUISTIC

SQL>

SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH

2  FROM DUAL;

1

还可以创建不区分大小写的索引：

CREATE INDEX

nlsci1_gen_person

ON

MY_PERSON

(NLSSORT

(PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')

)

;

此信息来自Oracle不区分大小写的搜索。文章提到了REGEXP_LIKE，但它似乎也适用于老款=。

在10gr2之前的版本中，不能真正做到这一点，通常的方法是，如果不需要不区分重音的搜索，只需UPPER()列和搜索表达式。

这很好，但是它使得使用like/=操作符的更新非常慢……：(

@saqibari任意LIKE表达式(如WHERE foo LIKE '%abc%'表达式)如果无法索引，已经足够慢了，我认为这与区分大小写无关。

我甚至试过用接线员。同样的慢性能。

您也可以在sqlplus之外设置这些，就像在shell环境中一样。例如，在使用DBD::Oracle的Perl脚本中，可以在调用'dbi->connect'之前编写$ENV{NLS_SORT} = 'BINARY_CI'; $ENV{NLS_COMP} = 'LINGUISTIC';。

嘿，ALTER SESSION是否只更改了您的本地更正实例，这是否意味着与您当前的会话类似，即如果我关闭并重新打开，它将重置。有没有一种方法可以让我看到当前值是什么，这样如果它在任何地方都持续存在，我就可以更改回原始设置…

也许你可以尝试使用

SELECT user_name

FROM user_master

WHERE UPPER(user_name) LIKE '%ME%'

已经试过了，不起作用

为什么不起作用？我觉得不错。

它在输入参数为整个大写时工作，如果为小写或混合，则不工作。

那你有没有想过WHERE upper(user_name) LIKE UPPER('%ME%')？：)

@塞吉奥尼，你也必须大写搜索词！

@Sergionni，那么为什么不在输入参数上使用UPPER？

@科内拉克，谢谢，你的方法是正确的

@Markus Winand谢谢，已经完成了

@Sergionni您还应该考虑基于函数的性能索引。

@马库斯·温南德：它领导着%。所以不会使用索引。

泽尔克姆达姆：

@Sergionni:这个表达式与upper没有关联，因为我已经说过了，在像"%me%"这样的大写字母中，除非您将表达式作为参数传递，并且您不控制它的大小写，否则不知upper将如何转换为任何特殊的值。

@v4vendeta：stackoverflow的一部分不仅仅是阅读它所说的，而是猜测它的含义。%ME%可能来自某个变量，但作者没有提到；)你当然是对的，如果%ME%字面上和where子句中的一样，UPPER(%ME%) == %ME%没有区别。

@v4vendeta使用UPPER函数会丢失索引，您知道如何使用索引进行搜索吗？

对于没有基于函数的索引的大型记录集来说，这将是非常缓慢的。

从Oracle 12c r2可以使用COLLATE operator：

The COLLATE operator determines the collation for an expression. This operator enables you to override the collation that the database would have derived for the expression using standard collation derivation rules.

The COLLATE operator takes one argument, collation_name, for which you can specify a named collation or pseudo-collation. If the collation name contains a space, then you must enclose the name in double quotation marks.

演示：

CREATE TABLE tab1(i INT PRIMARY KEY, name VARCHAR2(100));

INSERT INTO tab1(i, name) VALUES (1, 'John');

INSERT INTO tab1(i, name) VALUES (2, 'Joe');

INSERT INTO tab1(i, name) VALUES (3, 'Billy');

--========================================================================--

SELECT /*csv*/ *

FROM tab1

WHERE name = 'jOHN' ;

-- no rows selected

SELECT /*csv*/ *

FROM tab1

WHERE name COLLATE BINARY_CI = 'jOHN' ;

/*

"I","NAME"

1,"John"

*/

SELECT /*csv*/ *

FROM tab1

WHERE name LIKE 'j%';

-- no rows selected

SELECT /*csv*/ *

FROM tab1

WHERE name COLLATE BINARY_CI LIKE 'j%';

/*

"I","NAME"

1,"John"

2,"Joe"

*/

Db>小提琴演示

SELECT user_name

FROM my_table

WHERE nlssort(user_name, 'NLS_SORT = Latin_CI') = nlssort('%AbC%', 'NLS_SORT = Latin_CI')

你的第二个NLSSORT的第一个论点中的%不应该是通配符，对吗？他们有点困惑。

你可以这样做：

WHERE regexp_like(name, 'string$', 'i');