Escape underscore in LIKE

Question

I'm using SQL Server 2008 and I have the following stored procedure :

ALTER PROC [dbo].[spGetLatestLangId]
    @salt VARCHAR(50),
    @result VARCHAR(50) OUTPUT
AS
    DECLARE @CheckStr AS VARCHAR(50)
    SET @CheckStr = @salt + '%'
    SET @result = (SELECT TOP 1 Id FROM Lang WHERE salt LIKE @CheckStr ORDER BY dtCrt DESC)

Let's say my Lang table have following rows:

Id            dtCrt
------------- ----------
PRODUCT_1     2011-01-01
PRODUCT_2     2011-01-02
PRODUCTCODE_1 2011-01-05

When I call the stored procedure with following parameter

EXEC spGetLatestLangId 'PRODUCT_', @a OUTPUT

I'm getting PRODUCTCODE_1 instead of PRODUCT_2 because it ignores the underscore. How do I escape underscore in LIKE clause?

possible duplicate of [SQL Server 2000/5 Escape an Underscore](http://stackoverflow.com/questions/5821/sql-server-2000-5-escape-an-underscore) — sqluser, Jun 17 '15 at 06:41

score 4 · Answer 1 · answered Jun 17 '15 at 06:51

how do I escape underscore in LIKE clause

Using the ESCAPE clause:

where some_column LIKE 'X/_%' ESCAPE '/';

in your case:

WHERE salt LIKE @CheckStr ESCAPE '/'

You can use any character for the escape character (e.g. ESCAPE '\' or ESCAPE '@') - it's a good idea to use one that is not part of the values.

But this means you also have to call the procedure with the escape character:

EXEC spGetLatestLangId 'PRODUCT/_', @a OUTPUT

score 3 · Answer 2 · answered Jun 17 '15 at 06:39

You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. The table shows several examples of using the LIKE keyword and the [ ] wildcard characters.

For your case:

... LIKE 'PRODUCT[_]%'

score 1 · Answer 3 · answered Jun 17 '15 at 06:40

_ is a wildcard in SQL Server. You can add a regex pattern to your String. Try this.

CREATE TABLE #temp(strings nvarchar(50))

INSERT INTO #temp(strings)
VALUES(N'PRODUCT_1'),(N'PRODUCT_2'),(N'PRODUCTCODE_1')

SELECT *
FROM #temp
WHERE strings LIKE N'PRODUCT[_]%'

DROP TABLE #temp

score 1 · Accepted Answer · answered Jun 17 '15 at 06:41

1

I think you need to use:

SET @CheckStr = REPLACE(@salt, '_', '[_]') + '%'

answered Jun 17 '15 at 06:41

shA.t

16,580
5
54
111

score 0 · Answer 5 · answered Jun 17 '15 at 06:41

0

In Sql server keywords and wildcard characters are used as normal words by enclosing in []

eg:- select * from [User]

     select * from [User] where name like 'John[_]%'

answered Jun 17 '15 at 06:41

Sachu

7,555
7
55
94

score 0 · Answer 6 · answered Jun 17 '15 at 06:43

0

Change your query

SELECT TOP 1 Id FROM Lang WHERE salt LIKE REPLACE(@CheckStr, '_', '[_]')  ORDER BY dtCrt DESC

answered Jun 17 '15 at 06:43

Mukesh Kalgude

4,814
2
17
32

score 0 · Answer 7 · answered Jun 17 '15 at 06:48

Just wrap '_' -> '[_]'

ALTER PROC [dbo].[spGetLatestLangId]
    @salt VARCHAR(50),
    @result VARCHAR(50) OUTPUT
AS
    DECLARE @CheckStr AS VARCHAR(50)
    SET @CheckStr = @salt + '%'
    SET @result = (SELECT TOP 1 Id FROM Lang WHERE salt LIKE REPLACE(@CheckStr,'_','[_]') ORDER BY dtCrt DESC)

Escape underscore in LIKE

7 Answers7