Geek Answers Handbook

How to use escape with LIKE in SQL Query

My request:

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) + ',%');

Square brackets and underscores block data sampling. My question is where should I put escape '\' in the last line of the query. I also tried the parameter , but did not work REPLACE

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) + ',%');
+4
source share
3 answers

First you need to escape all LIKE characters and use ESCAPE . In addition, your conversion of column B to VARCHAR is by default only VARCHAR (30), so the data in column B has been truncated and does not match, since the @ param2 parameter alone is 35 characters.

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 '|';

:, ColumnB , "[Test_data_forQry]", , , ColumnB @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 '|';

, , , , ColumnA = 1 ColumnB = '[Test_data_forQry].

Update: , ( , ):

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 '|';
0

, . :

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]'

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

0

" :

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

Note:
SET QUOTED_IDENTIFIER = ON => By default, If SET QUOTED_IDENTIFIER ON, identifiers can be separated by double quotes, and literals must be separated by single quotes. If SET QUOTED_IDENTIFIER OFF, identifiers cannot be specified and must follow all Transact-SQL rules for identifiers.

0
source

More articles:


All Articles