Le serveur SQL ignore la casse dans une expression where

Question

Comment construire une requête SQL (MS SQL Server) où la clause "where" est insensible à la casse ?

SELECT * FROM myTable WHERE myField = 'sOmeVal'

Je veux que les résultats reviennent en ignorant l'affaire.

Answer 1

Dans la configuration par défaut d'une base de données SQL Server, les comparaisons de chaînes de caractères sont insensible à la casse. Si votre base de données remplace ce paramètre (par l'utilisation d'une autre collation), vous devrez alors spécifier le type de collation à utiliser dans votre requête.

SELECT * FROM myTable WHERE myField = 'sOmeVal' COLLATE SQL_Latin1_General_CP1_CI_AS

Notez que la collation que j'ai fournie n'est qu'un exemple (bien qu'il soit plus que probable qu'elle fonctionne parfaitement pour vous). Un aperçu plus complet des collations du serveur SQL est disponible à l'adresse suivante ici .

Answer 2

En général, les comparaisons de chaînes sont insensibles à la casse. Si votre base de données est configurée avec une collation sensible à la casse, vous devez forcer l'utilisation d'une collation insensible à la casse :

SELECT balance FROM people WHERE email = 'billg@microsoft.com'
  COLLATE SQL_Latin1_General_CP1_CI_AS

Answer 3

Les deux premières réponses (de Adam Robinson et Andrejs Cainikovs ) sont en quelque sorte corrects, dans la mesure où ils fonctionnent techniquement, mais leurs explications sont erronées et peuvent donc être trompeuses dans de nombreux cas. Par exemple, alors que le SQL_Latin1_General_CP1_CI_AS fonctionnera dans de nombreux cas, elle ne doit pas être considérée comme la collation insensible à la casse appropriée. En fait, étant donné que l'O.P. travaille dans une base de données avec une collation sensible à la casse (ou éventuellement binaire), nous savons que l'O.P. n'utilise pas la collation qui est la valeur par défaut pour de nombreuses installations (en particulier celles installées sur un système d'exploitation utilisant l'anglais américain comme langue) : SQL_Latin1_General_CP1_CI_AS . Bien sûr, l'O.P. pourrait utiliser SQL_Latin1_General_CP1_CS_AS mais lorsqu'on travaille avec VARCHAR il est important de ne pas modifier la page de codes, car cela pourrait entraîner une perte de données, et cela est contrôlé par la locale / culture de la collation (c'est-à-dire Latin1_General vs Français vs Hébreu etc). Veuillez consulter le point 9 ci-dessous.

Les quatre autres réponses sont fausses à des degrés divers.

Je vais clarifier tous les malentendus ici afin que les lecteurs puissent, je l'espère, faire les choix les plus appropriés / efficaces.

1. Ne pas utiliser UPPER() . C'est un travail supplémentaire totalement inutile. Utilisez un COLLATE clause. Une comparaison de chaînes de caractères doit être effectuée dans les deux cas, mais l'utilisation de la clause UPPER() doit également vérifier, caractère par caractère, s'il existe une correspondance avec une majuscule, puis la modifier. Et vous devez faire cela des deux côtés. Ajout de COLLATE demande simplement au traitement de générer les clés de tri en utilisant un ensemble de règles différent de celui qu'il utilisait par défaut. Utilisation de COLLATE est définitivement plus efficace (ou "performant", si vous aimez ce mot :) que l'utilisation de la fonction UPPER() comme le prouve cette test script (sur PasteBin) .

   Il y a aussi la question noté par @Ceisc sur la réponse de @Danny :

   Dans certains langages, les conversions de cas ne s'inversent pas, c'est-à-dire LOWER(x) != LOWER(UPPER(x)).

   La majuscule turque "İ" est un exemple courant.

2. Non, le collationnement n'est pas un paramètre à l'échelle de la base de données, du moins pas dans ce contexte. Il existe une collation par défaut au niveau de la base de données, et elle est utilisée comme valeur par défaut pour les colonnes modifiées et nouvellement créées qui ne spécifient pas l'attribut COLLATE (ce qui est probablement à l'origine de cette idée fausse), mais elle n'a pas d'impact direct sur les requêtes, sauf si vous comparez des chaînes de caractères et des variables à d'autres chaînes de caractères et variables, ou si vous faites référence à des métadonnées au niveau de la base de données.

3. Non, la collation n'est pas par requête.

4. Les collations sont par prédicat (c'est-à-dire quelque chose opérande quelque chose) ou expression, et non par requête. Et ceci est vrai pour l'ensemble de la requête, pas seulement pour l'élément WHERE clause. Cela couvre les JOIN, GROUP BY, ORDER BY, PARTITION BY, etc.

5. Non, ne pas convertir en VARBINARY (par exemple convert(varbinary, myField) = convert(varbinary, 'sOmeVal') ) pour les raisons suivantes :

   1. il s'agit d'une comparaison binaire, qui n'est pas insensible à la casse (ce qui est le but de la question).
   2. si vous voulez une comparaison binaire, utilisez une collation binaire. Utilisez une collation qui se termine par _BIN2 si vous utilisez SQL Server 2008 ou une version plus récente, sinon vous n'avez pas d'autre choix que d'utiliser celui qui se termine par _BIN . Si les données sont NVARCHAR alors la locale que vous utilisez n'a pas d'importance car elles sont toutes identiques dans ce cas, d'où Latin1_General_100_BIN2 fonctionne toujours. Si les données sont VARCHAR vous devez utiliser la même locale que celle dans laquelle les données se trouvent actuellement (par ex. Latin1_General , French , Japanese_XJIS ) parce que la locale détermine la page de code utilisée et que le changement de page de code peut altérer les données (c'est-à-dire entraîner une perte de données).
   3. L'utilisation d'un type de données de longueur variable sans spécifier la taille se basera sur la taille par défaut, et il existe deux valeurs par défaut différentes selon le contexte dans lequel le type de données est utilisé. Il s'agit de 1 ou 30 pour les types de chaînes de caractères. Lorsqu'il est utilisé avec CONVERT() il utilisera la valeur par défaut de 30. Le danger est que, si la chaîne peut dépasser 30 octets, elle sera tronquée silencieusement et vous obtiendrez probablement des résultats incorrects avec ce prédicat.
   4. Même si vous voulez une comparaison sensible à la casse, les collations binaires sont pas sensible à la casse (autre idée fausse très répandue).

6. Non, LIKE n'est pas toujours sensible à la casse. Il utilise la collation de la colonne référencée, ou la collation de la base de données si une variable est comparée à une chaîne littérale, ou encore la collation spécifiée par l'attribut facultatif COLLATE clause.

7. LCASE n'est pas une fonction du serveur SQL. Il semble que ce soit Oracle ou MySQL. Ou peut-être Visual Basic ?

8. Puisque le contexte de la question est la comparaison d'une colonne à une chaîne littérale, ni la collation de l'instance (souvent appelée "serveur") ni la collation de la base de données n'ont d'importance. direct impact ici. Les collations sont stockées pour chaque colonne, et chaque colonne peut avoir une collation différente, et ces collations n'ont pas besoin d'être les mêmes que la collation par défaut de la base de données ou que la collation de l'instance. Bien sûr, la collation de l'instance est la collation par défaut qu'une base de données nouvellement créée utilisera comme collation par défaut si l'attribut COLLATE n'a pas été spécifiée lors de la création de la base de données. De même, la collation par défaut de la base de données est celle qu'une colonne modifiée ou nouvellement créée utilisera si la clause COLLATE n'a pas été spécifié.

9. Vous devez utiliser la collation insensible à la casse qui est par ailleurs la même que la collation de la colonne. Utilisez la requête suivante pour trouver la collation de la colonne (changez le nom de la table et le nom du schéma) :

   SELECT col.*
   FROM   sys.columns col
   WHERE  col.[object_id] = OBJECT_ID(N'dbo.TableName')
   AND    col.[collation_name] IS NOT NULL;

   Il suffit alors de modifier le _CS à être _CI . Donc, Latin1_General_100_CS_AS deviendrait Latin1_General_100_CI_AS .

   Si la colonne utilise une collation binaire (se terminant par _BIN ou _BIN2 ), puis trouvez une collation similaire en utilisant la requête suivante :

   SELECT *
   FROM   sys.fn_helpcollations() col
   WHERE  col.[name] LIKE N'{CurrentCollationMinus"_BIN"}[_]CI[_]%';

   Par exemple, en supposant que la colonne utilise Japanese_XJIS_100_BIN2 faites ça :

   SELECT *
   FROM   sys.fn_helpcollations() col
   WHERE  col.[name] LIKE N'Japanese_XJIS_100[_]CI[_]%';

Pour plus d'informations sur les collations, les encodages, etc., veuillez consulter le site : Infos sur les collations

Answer 4

J'ai trouvé une autre solution ailleurs ; c'est d'utiliser

upper(@yourString)

mais tout le monde ici dit que, dans SQL Server, cela n'a pas d'importance parce qu'il s'agit d'ignorer la casse de toute façon ? Je suis presque sûr que notre base de données est sensible à la casse.

Answer 5

Non, seulement en utilisant LIKE ne fonctionnera pas. LIKE recherche les valeurs correspondant exactement à votre modèle donné. Dans ce cas LIKE ne trouverait que le texte "sOmeVal" et non "someval".

Une solution pratique consiste à utiliser l'option LCASE() fonction. LCASE('sOmeVal') obtient la chaîne de caractères en minuscules de votre texte : 'someval'. Si vous utilisez cette fonction pour les deux côtés de votre comparaison, cela fonctionne :

SELECT * FROM myTable WHERE LCASE(myField) LIKE LCASE('sOmeVal')

L'instruction compare deux chaînes de caractères minuscules, de sorte que votre "sOmeVal" correspondra à toute autre notation de "someval" (par exemple "Someval", "sOMEVAl", etc.).