select * from t where t.content like N'%%%' escape '%'
select * from t where content like '%!%%' escape '!'
这个是可以, 但是程序中是个sql模板字符串WHERE content like N'%_KEYWORD%' 调用时,将_KEYWORD置换为用户输入的字符串,所以使用charindex只能解决这一个问题,不能通用啊
DECLARE @keyword varchar(100) SET @keyword = '%'SET @keyword = REPLACE(@keyword,'\','\\') SET @keyword = REPLACE(@keyword,'%','\%') SET @keyword = REPLACE(@keyword,'_','\_') SET @keyword = REPLACE(@keyword,'[','\[') SET @keyword = REPLACE(@keyword,']','\]')SELECT * FROM ( SELECT string = 'abc\de' UNION ALL SELECT string = 'abc%de' UNION ALL SELECT string = 'abc_de' UNION ALL SELECT string = 'abc[de' UNION ALL SELECT string = 'abc]de' UNION ALL SELECT string = 'abc[^d]e' UNION ALL SELECT string = 'ab[c-d]e' ) tmp WHERE string LIKE '%' + @keyword + '%' ESCAPE '\'
select * from t where t.content like N'%%%' escape '%'
select * from t where t.content like N'%[%]%' 将%用[]括起来就OK了,在程序调用时,将where条件中的所有的的%替换为[%]。
但是程序中是个sql模板字符串WHERE content like N'%_KEYWORD%'
调用时,将_KEYWORD置换为用户输入的字符串,所以使用charindex只能解决这一个问题,不能通用啊
DECLARE @keyword varchar(100)
SET @keyword = '%'SET @keyword = REPLACE(@keyword,'\','\\')
SET @keyword = REPLACE(@keyword,'%','\%')
SET @keyword = REPLACE(@keyword,'_','\_')
SET @keyword = REPLACE(@keyword,'[','\[')
SET @keyword = REPLACE(@keyword,']','\]')SELECT *
FROM (
SELECT string = 'abc\de' UNION ALL
SELECT string = 'abc%de' UNION ALL
SELECT string = 'abc_de' UNION ALL
SELECT string = 'abc[de' UNION ALL
SELECT string = 'abc]de' UNION ALL
SELECT string = 'abc[^d]e' UNION ALL
SELECT string = 'ab[c-d]e'
) tmp
WHERE string LIKE '%' + @keyword + '%' ESCAPE '\'
将%用[]括起来就OK了,在程序调用时,将where条件中的所有的的%替换为[%]。