How to use escape with LIKE in SQL Query

Question

My Query is :

declare @param1 INT,
@param2 nvarchar(max)

set @param1 = 1
set @param2 = '[Test_data_forQry],[Next_Test_Data]'

SELECT * 
FROM Table1
WHERE
    ColumnA = @param1  
AND (',' + @param2 +',' LIKE '%,' + CONVERT(VARCHAR, ColumnB) + ',%');

The square brackets and underscore is blocking the data fetch. My question is where should I place escape '\' in the last line of query. I also tried REPLACE option, but didn't work

set @param1 = 1
set @param2 = '[Test_data_forQry],[Next_Test_Data]'

set @param2  = replace (replace(@param2  , '[', '[[]'), '_','[_]') 

SELECT * 
FROM Table1
WHERE
    ColumnA = @param1  
AND (',' + @param2 +',' LIKE '%,' + CONVERT(VARCHAR, ColumnB) + ',%');

The square brackets and underscore in @param2 are not the problem. The problem is that those characters are in ColumnB, after the `LIKE`. What is in ColumnB, a single bracketed name like `[Test_data_forQry]`? — Matthew Jaspers, May 29 '15 at 16:01
Yes, ColumB has data like [Test_data_forQry] .. a direct query fetches data like select * from Table where ColumnA=1 and ColumnB like '%[[]Test[_]data[_]forQry]%' — user3315245, May 29 '15 at 16:03
Use CHARINDEX insstead of LIKE, when you search values with "[]" — Tim3880, May 29 '15 at 16:07
I don't know what is the correction needed in the below qry, i'm only getting one record using Charindex Modified Query : select * FROM Table1 WHERE ColumnA = param1 AND CHARINDEX(',' + CONVERT(VARCHAR, ColumnB) +',' , param2 )>0; — user3315245, May 29 '15 at 17:43
The XML usage seems to work Declare Ids varchar(50) Set Ids = ‘1,2,3,5,4,6,7,98,234’ DECLARE XML XML SET XML = CAST('' + REPLACE(@Ids, ',', '') + '' AS XML) SELECT * FROM SomeTable INNER JOIN XML.nodes('i') x(i) ON SomeTable .Id = x.i.value('.', 'VARCHAR(MAX)') http://stackoverflow.com/a/16936683/3315245 — user3315245, May 29 '15 at 17:46

score 0 · Answer 1 · edited May 23 '17 at 10:26

You need to escape all LIKE characters first and use ESCAPE operator. Also, your conversion of ColumnB to VARCHAR is by default only VARCHAR(30) so data in ColumnB was being truncated and not matching since, by itself, @param2 is 35 characters unescaped.

See below:

DECLARE @param1 INT, @param2 NVARCHAR(MAX);

SET @param1 = 1
SET @param2 = '[Test_data_forQry],[Next_Test_Data]'

-- Replace LIKE special characters
SET @param2 = REPLACE(REPLACE(REPLACE(REPLACE(@param2, '[', '|['), ']', '|]'), '%', '|%'), '_', '|_')

SELECT *
FROM Table1
WHERE
    ColumnA = @param1  
    AND CONVERT(NVARCHAR(MAX), ColumnB) LIKE ('%,' + @param2 +',%') ESCAPE '|';

Edit: if your ColumnB data does have example data like '[Test_data_forQry]' that you indicated in one of you comments, then leave it switched around like so and escape ColumnB instead of @param2:

DECLARE @param1 INT, @param2 NVARCHAR(MAX);

SET @param1 = 1
SET @param2 = '[Test_data_forQry],[Next_Test_Data]'

SELECT *
FROM Table1
WHERE
    ColumnA = @param1  
    AND @param2 LIKE '%' + REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), ColumnB), '[', '|['), ']', '|]'), '%', '|%'), '_', '|_') + '%' ESCAPE '|';

Not great for performance likely with so much happening in the where, but this will match a row with ColumnA = 1 and ColumnB = '[Test_data_forQry]'.

Update: Updated to split the search string on commas (will likely need to create a function from here first):

DECLARE @param1 INT, @param2 NVARCHAR(MAX);

SET @param1 = 1
SET @param2 = '[Test_data_forQry],[Next_Test_Data]'

-- Replace LIKE special characters
SET @param2 = REPLACE(REPLACE(REPLACE(REPLACE(@param2, '[', '|['), ']', '|]'), '%', '|%'), '_', '|_')

SELECT DISTINCT t.*
FROM Table1 t
    INNER JOIN dbo.split(@param2, ',') split ON 1=1
WHERE
    t.ColumnA = @param1  
    AND CONVERT(NVARCHAR(MAX), t.ColumnB) LIKE ('%' + split.s + '%') ESCAPE '|';

Yes it fetches only the first match [Test_data_forQry] but I need the qry to get all the data matching with param2 SET param2 = '[Test_data_forQry],[Next_Test_Data]' — user3315245, Jun 02 '15 at 14:23
Do you mean you have data in ColumnB that could contain [Test_data_forQry] _or_ [Next_Test_Data] and you want to search on that? — Jordan Parker, Jun 04 '15 at 02:52

score 0 · Answer 2 · answered May 29 '15 at 23:07

I was able to do it by making the left square-bracket a literal. Try this:

declare @table table (column1 varchar(100))
insert @table(column1) values('[square_brackets_1]')
insert @table(column1) values('[square_brackets_2]')
insert @table(column1) values('[square_brackets_3]')
insert @table(column1) values('[square_brackets_4]')
insert @table(column1) values('[square_brackets_5]')

select * from @table

select * from @table where column1 like '[[]square_brackets_5]'

reference: https://msdn.microsoft.com/en-us/library/ms179859.aspx

score 0 · Answer 3 · answered May 30 '15 at 13:49

0

I suggest you to use " like this:

set @param2 = '"' + REPLACE(@param2, ',' , '","') + '"'

Note :
SET QUOTED_IDENTIFIER = ON => Default
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

answered May 30 '15 at 13:49

shA.t

16,580
5
54
111

Thank you for suggestion. It did not get data. – user3315245 Jun 02 '15 at 14:19

How to use escape with LIKE in SQL Query

3 Answers3