Escape uma string no SQL Server para que seja seguro usar na expressão LIKE

Como faço para escaping uma seqüência de caracteres no procedimento armazenado do SQL Server para que seja seguro usar na expressão LIKE .

Suponha que eu tenha uma variável NVARCHAR assim:

 declare @myString NVARCHAR(100); 

E eu quero usá-lo em uma expressão LIKE :

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

Como faço para escaping da string (mais especificamente, caracteres que são significativos para a correspondência de padrões LIKE , por exemplo, % ou ? ) Em T-SQL, para que seja seguro usar dessa maneira?

Por exemplo, dado:

 @myString = 'aa%bb' 

Eu quero:

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

para combinar 'aa%bb' , 'caa%bbc' mas não 'aaxbb' ou 'caaxbb' .

Para escaping de caracteres especiais em uma expressão LIKE, você os prefixará com um caractere de escape. Você pode escolher qual char de escape usar com a palavra-chave ESCAPE. ( MSDN Ref )

Por exemplo, isso escapa do símbolo%, usando \ como o caractere de escape:

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

Se você não souber quais caracteres estarão em sua string e não quiser tratá-los como curingas, poderá prefixar todos os caracteres curinga com um caractere de escape, por exemplo:

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

(Note que você tem que escaping do seu caractere de escape também, e certifique-se de que é a replace interna para que você não escape daqueles adicionados das outras instruções de replace ). Então você pode usar algo assim:

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

Lembre-se também de alocar mais espaço para sua variável @myString, pois ela se tornará mais longa com a substituição da string.

Tive um problema semelhante (usando o NHibernate, então a palavra-chave ESCAPE teria sido muito difícil) e resolvi usando os caracteres de colchetes. Então sua amostra se tornaria

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

Se você precisar de prova:

 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 

Em vez de escaping de todos os caracteres em uma string que tenham um significado particular na syntax do padrão, dado que você está usando um caractere curinga principal no padrão, é mais rápido e mais fácil de fazer.

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

Nos casos em que você não estiver usando um curinga principal, a abordagem acima deve ser evitada, pois não pode usar um índice em YourColumn .

Além disso, nos casos em que o plano de execução ideal varia de acordo com o número de linhas correspondentes, as estimativas podem ser melhores ao usar o LIKE com a syntax de escape do colchete quando comparado a CHARINDEX e à palavra-chave ESCAPE .

Você especifica o caractere de escape. Documentação aqui:
http://msdn.microsoft.com/pt-br/library/ms179859.aspx

Você quer procurar por strings que incluam um caractere de escape? Por exemplo, você quer isso:

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

Onde você quer procurar todos os campos com 10%? Se esse for o caso, você poderá usar a cláusula ESCAPE para especificar um caractere de escape e escaping do caractere curinga.

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