Экранируйте строку в SQL Server, чтобы ее можно было безопасно использовать в выражении LIKE

как мне избежать строки в хранимой процедуре SQL Server, чтобы ее можно было безопасно использовать в LIKE выражение.

Предположим, у меня есть NVARCHAR переменной вот так:

declare @myString NVARCHAR(100);

и я хочу использовать его в LIKE выражение:

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

как мне избежать строки (более конкретно, символы, которые имеют значение для LIKE сопоставление с образцом, например,% или ?) в T-SQL, так что это безопасно использовать в этом манеры?

например, дано:

@myString = 'aa%bb'

хочу:

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

в матче 'aa%bb','caa%bbc' а не 'aaxbb' или 'caaxbb'.

5 68
sql-server tsql sql-like stored-procedures

5 ответов:

чтобы избежать специальных символов как выражения префикса с Escape-символ. Вы можете выбрать, какой escape-символ использовать с ключевым словом ESCAPE. ( MSDN Ref)

например, это экранирует символ%, используя \ в качестве escape char:

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

если вы не знаете, какие символы будут в вашей строке, и вы не хотите рассматривать их как подстановочные знаки, вы можете префиксировать все подстановочные знаки с помощью escape-символа, например: 

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

(обратите внимание, что вы должны избежать вашего побега char тоже, и убедитесь, что это внутренний replace Так что вы не избежать те, которые добавлены из других replace заявления). Затем вы можете использовать что-то вроде этого: 

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

также не забудьте выделить больше места для переменной @myString, поскольку она станет длиннее с заменой строки.

83

была аналогичная проблема (используя NHibernate, поэтому ключевое слово ESCAPE было бы очень сложно) и решило ее с помощью символов скобок. Так что ваш образец станет

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

Если вам нужны доказательства:

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
17

вместо того, чтобы бежать все символы в строке, которые имеют особое значение в синтаксисе шаблона учитывая, что вы используете ведущих подстановочный знак в шаблоне это быстрее и проще всего сделать.

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

в случаях, когда вы не используете ведущий подстановочный знак, следует избегать подхода выше, поскольку он не может использовать индекс на YourColumn.

кроме того, в случаях, когда оптимальный план выполнения будет варьироваться в зависимости от количества соответствующие строки оценки могут быть лучше при использовании LIKE с квадратной скобкой, экранирующей синтаксис по сравнению с и CHARINDEX и the ESCAPE ключевое слово.

13

вы указываете escape-символ. Документация здесь:
http://msdn.microsoft.com/en-us/library/ms179859.aspx

4

вы хотите искать строки, содержащие escape-символ? Например, вы хотите этого:

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

где вы хотите искать все поля с 10%? Если это так, то вы можете использовать ESCAPE-предложение, чтобы указать escape-символ и избежать подстановочного знака.

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