Échapper une chaîne de caractères dans SQL Server pour qu'elle puisse être utilisée en toute sécurité dans une expression LIKE

Question

Comment échapper une chaîne de caractères dans une procédure stockée du serveur SQL pour qu'elle puisse être utilisée en toute sécurité dans les procédures stockées du serveur SQL ? LIKE l'expression.

Supposons que je dispose d'un NVARCHAR variable comme suit :

declare @myString NVARCHAR(100);

Et je veux l'utiliser dans un LIKE l'expression :

... WHERE ... LIKE '%' + @myString + '%';

Comment échapper à la chaîne de caractères (plus précisément, aux caractères significatifs pour les LIKE la recherche de motifs, par exemple % o ? ) en T-SQL, afin qu'il puisse être utilisé en toute sécurité de cette manière ?

Par exemple, étant donné :

@myString = 'aa%bb'

Je veux :

WHERE ... LIKE '%' + @somehowEscapedMyString + '%'

pour correspondre 'aa%bb' , 'caa%bbc' mais pas 'aaxbb' o 'caaxbb' .

Answer 1

Pour échapper aux caractères spéciaux dans une expression LIKE, il faut les faire précéder d'un caractère d'échappement. Vous pouvez choisir le caractère d'échappement à utiliser avec le mot-clé ESCAPE. ( MSDN Ref )

Par exemple, ceci échappe le symbole %, en utilisant \N comme caractère d'échappement :

select * from table where myfield like '%15\% off%' ESCAPE '\'

Si vous ne savez pas quels caractères figureront dans votre chaîne et que vous ne voulez pas les traiter comme des caractères génériques, vous pouvez faire précéder tous les caractères génériques d'un caractère d'échappement, par exemple :

set @myString = replace( 
                replace( 
                replace( 
                replace( @myString
                ,    '\', '\\' )
                ,    '%', '\%' )
                ,    '_', '\_' )
                ,    '[', '\[' )

(Notez que vous devez également échapper à votre caractère d'échappement, et assurez-vous qu'il s'agit bien du caractère d'échappement interne). replace afin de ne pas échapper à ceux qui ont été ajoutés à l'autre replace ). Vous pouvez alors utiliser quelque chose comme ceci :

select * from table where myfield like '%' + @myString + '%' ESCAPE '\'

N'oubliez pas non plus d'allouer plus d'espace à votre variable @myString, car elle deviendra plus longue avec le remplacement de la chaîne.

Answer 2

J'ai eu un problème similaire (en utilisant NHibernate, donc le mot clé ESCAPE aurait été très difficile) et je l'ai résolu en utilisant les caractères entre crochets. Votre exemple deviendrait donc

WHERE ... LIKE '%aa[%]bb%'

Si vous avez besoin de preuves :

create table test (field nvarchar(100))
go
insert test values ('abcdef%hijklm')
insert test values ('abcdefghijklm')
go
select * from test where field like 'abcdef[%]hijklm'
go

Answer 3

Plutôt que de s'échapper todos les caractères d'une chaîne qui ont une signification particulière dans la syntaxe du motif étant donné que vous utilisez un caractère générique en tête du motif, il est plus rapide et plus facile de le faire.

SELECT * 
FROM YourTable
WHERE CHARINDEX(@myString , YourColumn) > 0

Dans les cas où vous n'utilisez pas de caractère générique, l'approche ci-dessus doit être évitée car elle ne permet pas d'utiliser un index sur le fichier YourColumn .

En outre, dans les cas où le plan d'exécution optimal varie en fonction du nombre de lignes correspondantes, les estimations peuvent être meilleures lorsque l'on utilise la fonction LIKE avec la syntaxe d'échappement des crochets, par rapport à à la fois CHARINDEX y les ESCAPE mot-clé .

Answer 4

Vous spécifiez le caractère d'échappement. Documentation ici :
http://msdn.microsoft.com/en-us/library/ms179859.aspx

Answer 5

Voulez-vous rechercher les chaînes de caractères qui contiennent un caractère d'échappement ? Par exemple, vous voulez ceci :

select * from table where myfield like '%10%%'.

Où voulez-vous rechercher tous les champs avec 10% ? Si c'est le cas, vous pouvez utiliser la clause ESCAPE pour spécifier un caractère d'échappement et échapper au caractère générique.

select * from table where myfield like '%10!%%' ESCAPE '!'