Recherche insensible à la casse dans Oracle

Le comportement par défaut de LIKE et des autres opérateurs de comparaison, = etc est sensible à la casse.

Est-il possible de les rendre insensibles à la casse?

187
demandé sur Lukasz Szozda 2011-03-22 15:18:07

6 réponses

Depuis 10gR2, Oracle permet d'affiner le comportement des comparaisons de chaînes par le réglage de la NLS_COMP et NLS_SORT les paramètres de session:

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

Vous pouvez également créer des index insensibles à la casse:

create index
   nlsci1_gen_person
on
   MY_PERSON
   (NLSSORT
      (PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')
   )
;

Cette information a été tirée de recherches insensibles à la casse D'Oracle . L'article mentionne REGEXP_LIKE mais il semble aussi fonctionner avec le bon vieux =.


Dans les versions antérieures à 10gR2, cela ne peut pas vraiment être fait et l'approche habituelle, si vous vous n'avez pas besoin deaccent-insensible search, c'est juste UPPER() à la fois la colonne et l'expression de recherche.

68
répondu Álvaro González 2014-02-11 08:41:07

Il existe 3 façons principales d'effectuer une recherche insensible à la casse dans Oracle sans utiliser d'index de texte intégral.

En fin de compte, la méthode que vous choisissez dépend de vos circonstances individuelles; la principale chose à retenir est que pour améliorer les performances, vous devez indexer correctement pour une recherche insensible à la casse.

1. Cas votre colonne et votre chaîne de manière identique.

Vous pouvez forcer toutes vos données à être le même cas en utilisant UPPER() ou LOWER():

select * from my_table where upper(column_1) = upper('my_string');

Ou

select * from my_table where lower(column_1) = lower('my_string');

Si column_1 n'est pas indexé sur upper(column_1) ou lower(column_1), selon le cas, cela peut forcer une analyse complète de la table. Pour éviter cela, vous pouvez créer un index basé sur la fonction .

create index my_index on my_table ( lower(column_1) );

Si vous utilisez LIKE, vous devez concaténer un % autour de la chaîne que vous recherchez.

select * from my_table where lower(column_1) LIKE lower('my_string') || '%';

Ce SQL Fiddle montre ce qui se passe dans toutes ces requêtes. Notez les plans expliquer, qui indiquent quand un l'indice est utilisé et quand il ne l'est pas.

2. Utilisez des expressions régulières.

À Partir d'Oracle 10g à partir de REGEXP_LIKE() est disponible. Vous pouvez spécifier le _match_parameter_ 'i', afin d'effectuer une recherche insensible à la casse.

Pour l'utiliser comme opérateur d'égalité, vous devez spécifier le début et la fin de la chaîne, qui est désignée par le signe carat et le dollar.

select * from my_table where regexp_like(column_1, '^my_string$', 'i');

Pour effectuer L'équivalent de LIKE, ceux-ci peuvent être retiré.

select * from my_table where regexp_like(column_1, 'my_string', 'i');

Soyez prudent avec cela car votre chaîne peut contenir des caractères qui seront interprétés différemment par le moteur d'expression régulière.

Ce SQL Fiddle vous montre le même exemple de sortie sauf en utilisant REGEXP_LIKE ().

3. Changement au niveau de la session.

Le paramètre nls_sort régit la séquence de classement pour l'ordre et les différents opérateurs de comparaison, y compris = Et LIKE. Vous pouvez spécifier un binaire, insensible à la casse, trier en modifiant la session. Cela signifie que chaque requête effectuée dans cette session effectuera des paramètres insensibles à la casse.

alter session set nls_sort=BINARY_CI

Il y a beaucoup d'informations supplémentaires autour de tri linguistique et recherche de chaînes Si vous voulez spécifier une langue différente, ou faire une recherche insensible à l'accent en utilisant BINARY_AI.

Vous devrez également modifier le paramètre nls_comp; pour citer:

Les opérateurs exacts et des clauses de requête qui obéissent au paramètre NLS_SORT dépend de la valeur du paramètre NLS_COMP. Si un opérateur ou la clause n'obéit pas à la valeur nls_sort, telle que déterminée par NLS_COMP, le classement utilisé est binaire.

La valeur par défaut de NLS_COMP est binaire; mais, LINGUISTIC spécifie Qu'Oracle doit faire attention à la valeur de NLS_SORT:

Comparaisons pour toutes les opérations SQL dans la clause WHERE et dans PL / SQL les blocs doivent utiliser le tri linguistique spécifié dans le NLS_SORT paramètre. Pour améliorer les performances, vous pouvez également définir un index linguistique sur la colonne pour laquelle vous voulez comparaison.

Donc, encore une fois, vous devez modifier la session

alter session set nls_comp=LINGUISTIC

Comme indiqué dans la documentation, vous pouvez créer un index linguistique pour améliorer les performances

create index my_linguistc_index on my_table 
   (NLSSORT(column_1, 'NLS_SORT = BINARY_CI'));
251
répondu Ben 2015-10-25 22:50:38

Peut-être que vous pouvez essayer d'utiliser

SELECT user_name
FROM user_master
WHERE upper(user_name) LIKE '%ME%'
40
répondu V4Vendetta 2011-03-22 12:23:30

À Partir d'Oracle 12c R2 vous pouvez utiliser COLLATE operator:

L'opérateur COLLATE détermine le classement d'une expression. Cet opérateur vous permet de remplacer le classement que la base de données aurait dérivé pour l'expression en utilisant des règles de dérivation de classement standard.

L'opérateur COLLATE prend un argument, collation_name, pour lequel vous pouvez spécifier un collation nommé ou un pseudo-collation. Si le nom du classement contient un espace, vous devez mettre le nom entre guillemets doubles.

Démo:

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"
*/
2
répondu Lukasz Szozda 2018-02-15 15:31:01
select user_name
from my_table
where nlssort(user_name, 'NLS_SORT = Latin_CI') = nlssort('%AbC%', 'NLS_SORT = Latin_CI')
1
répondu Clodoaldo Neto 2011-03-22 12:32:15

Vous pouvez faire quelque chose comme ça:

where regexp_like(name, 'string$', 'i');
1
répondu grep 2014-11-19 14:40:10