CODEWITHSUNDEEP
sql-serversql-server-2008-r2
benni_mac_b
benni_mac_b

Reputation: 8877

LIKE and Equals not working as expected

I am currently trying to find all users in a database where there is a left bracket [ in the user name however when performing a query against the user table using the LIKE operator no rows are returned. If I use the equals = operator then rows are returned.

The issue doesn't appear when using the right bracket ] or other special characters. I want to use the LIKE keyword as I eventually want to use the wildcard functionality to find all users in the table which the character in.

I have isolated the problem out to an example below. FYI Collation is Latin1_General_BIN

-- [ Not Working
DECLARE @Temp TABLE (UserID VARCHAR(10))
INSERT INTO @Temp SELECT 'TEST[DEPT'
SELECT * FROM @Temp WHERE UserID = 'TEST[DEPT' --Returns 1 row
SELECT * FROM @Temp WHERE UserID LIKE 'TEST[DEPT' -- Returns 0 rows

-- ] Working
DECLARE @Temp2 TABLE (UserID VARCHAR(10))
INSERT INTO @Temp2 SELECT 'TEST]DEPT'
SELECT * FROM @Temp2 WHERE UserID = 'TEST]DEPT' --Returns 1 row
SELECT * FROM @Temp2 WHERE UserID LIKE 'TEST]DEPT' -- Returns 1 row

Upvotes: 2

Views: 423

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93694

SELECT * FROM @Temp WHERE UserID LIKE 'TEST[DEPT'

The query 'delimits' the special character with square brackets, telling the engine to treat it as a normal literal character instead of a character with special meaning. Check here for more info.

Try something like this.

SELECT * FROM @Temp WHERE UserID LIKE '%TEST[[]DEPT%'

Upvotes: 1

user330315
user330315

Reputation:

This is because SQL Server's LIKE doesn't comply with the SQL standard and uses an extended "pattern matching" that is a limited subset of a regular expression matching. And because of that the [ has a special meaning in SQL Server (unlike standard SQL) so the [ needs to be escaped.

So you need to use this:

WHERE UserID LIKE 'TEST\[DEPT' ESCAPE '\'

The escape character can be any character you want and it should be one that doesn't occur in the actual value.

Upvotes: 1

jpw
jpw

Reputation: 44871

Citing the documentation forLIKE:

Using Wildcard Characters As Literals

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.

So do this: SELECT * FROM @Temp WHERE UserID LIKE '%[[]%' if you want to match all rows with a left bracket somewhere in the UserID.

Upvotes: 2

Related Questions