Recherche insensible à la casse dans Oracle

Question

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 ?

Answer 1

Il y a trois façons principales d'effectuer une recherche insensible à la casse dans Oracle sans utiliser les index plein texte.

En fin de compte, le choix de la méthode dépend de votre situation personnelle ; la principale chose à retenir est que pour améliorer les performances, vous devez indexer correctement pour une recherche insensible à la casse.

1. Mettez 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() o LOWER() :

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

o

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

Si column_1 n'est pas indexé sur upper(column_1) o lower(column_1) Le cas échéant, cela peut forcer un balayage complet de la table. Afin d'éviter cela, vous pouvez créer un indice fonctionnel .

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

Si vous utilisez la méthode LIKE, vous devez alors concaténer un % autour de la chaîne de caractères que vous recherchez.

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

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

2. Utilisez des expressions régulières.

À partir d'Oracle 10g REGEXP_LIKE() est disponible. Vous pouvez spécifier le _paramètre de correspondance_. '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 sont indiqués par le carat et le signe dollar.

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

Afin de réaliser l'équivalent de LIKE, ces derniers peuvent être supprimés.

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

Soyez prudent 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 tripotage SQL vous montre le même exemple de sortie, mais en utilisant REGEXP_LIKE().

3. Modifiez-le au niveau de la session.

El NLS_SORT régit la séquence de collationnement pour l'ordonnancement et les différents opérateurs de comparaison, notamment = et LIKE. Vous pouvez spécifier un tri binaire, insensible à la casse, en modifiant la session. Cela signifie que chaque requête effectuée dans cette session exécutera des paramètres insensibles à la casse.

alter session set nls_sort=BINARY_CI

Il y a beaucoup d'informations supplémentaires autour tri linguistique et recherche de chaînes de caractères si vous souhaitez spécifier une autre langue, ou effectuer une recherche insensible aux accents en utilisant BINARY_AI.

Vous devrez également modifier le NLS_COMP paramètre ; à citer :

Les opérateurs exacts et les clauses de requête qui obéissent au paramètre NLS_SORT dépendent de la valeur du paramètre NLS_COMP. Si un opérateur ou une clause n'obéit pas à la valeur NLS_SORT, telle que déterminée par NLS_COMP, la collation utilisée est BINARY.

La valeur par défaut de NLS_COMP est BINARY ; 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 les blocs PL/SQL doivent utiliser le tri linguistique spécifié dans le paramètre NLS_SORT pour le tri linguistique. Pour améliorer les performances, vous pouvez également définir un index linguistique sur la colonne pour laquelle vous souhaitez un tri comparaisons linguistiques.

Donc, une fois de plus, vous devez modifier la session

alter session set nls_comp=LINGUISTIC

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

create index my_linguistc_index on my_table 
   (NLSSORT(column_1, 'NLS_SORT = BINARY_CI'));

Answer 2

Depuis 10gR2, Oracle permet d'affiner le comportement des comparaisons de chaînes de caractères en définissant l'attribut NLS_COMP y NLS_SORT les paramètres de la 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')
   )
;

Ces informations ont été tirées de Recherches Oracle insensibles à la casse . L'article mentionne REGEXP_LIKE mais cela semble fonctionner avec le bon vieux = également.

---

Dans les versions antérieures à 10gR2, il n'est pas vraiment possible de le faire et l'approche habituelle, si vous n'avez pas besoin d'un système de gestion de l'information, est la suivante insensible aux accents recherche, est de juste UPPER() à la fois la colonne et l'expression de recherche.

Answer 3

Vous pouvez peut-être essayer d'utiliser

SELECT user_name
FROM user_master
WHERE upper(user_name) LIKE '%ME%'

Answer 4

Nous vous rappelons amicalement que certains des exemples de recherche entraîneront un balayage complet de la table même s'il existe un index sur nom_utilisateur.

Answer 5

select user_name
from my_table
where nlssort(user_name, 'NLS_SORT = Latin_CI') = nlssort('%AbC%', 'NLS_SORT = Latin_CI')