SQL Like operator not working if underscore at first char

Question

I am not getting the exact record if string has underscore is a first char Example:

declare @name nvarchar(max)
set @name='_#@#_1'
SELECT Name from Emp where Name like  @name + '%'

Expected Output: It should return a single row as per table records (_#@#_123) but returning below records

_#@#_123
@#@#@123
@#@#_123
_#@#_123

Pease refer to [Using Wildcard Characters As Literals](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver15#using-wildcard-characters-as-literals) — Squirrel, Apr 08 '21 at 06:07
Thank you. But I will not have always a underscore as first char — Jethij, Apr 08 '21 at 06:13
The underscore is the wildcard for a single character so basically the results you showed match your searchstring... — Tyron78, Apr 08 '21 at 06:43
Thank you Tyron78. But the 4 results are unique. I want only the matching record starting with all the chars — Jethij, Apr 08 '21 at 06:52
@Jethij yes, I understood - therefore please have a look at my answer: I would suggest using substring and = instead of `LIKE` in this case. — Tyron78, Apr 08 '21 at 07:01
https://stackoverflow.com/questions/5821/sql-server-escape-an-underscore — Mitch Wheat, Apr 08 '21 at 07:02

George Menoutis · Answer 1 · 2021-04-08T07:23:12.183

2

Since underscore is a special character, you'll have to escape it using the character of your choice. Let's use a backslash \. You'll have to both 1) escape it in your data and 2) add the ESCAPE clause:

declare @name nvarchar(max)
set @name='_#@#_1'
SELECT Name from Emp where Name like 
  replace(replace(@name, '\', '\\'), '_', '\_') + '%' ESCAPE '\'

edited Apr 08 '21 at 07:23

answered Apr 08 '21 at 06:57

George Menoutis

6,894
3
19
43

1

And for the case the escape character itself can occur in the strings, escape that as well: `replace(replace(@name, '\', '\\'), '_', '\_')`. – Thorsten Kettner Apr 08 '21 at 07:18

score 0 · Answer 2 · answered Apr 08 '21 at 06:53

As mentioned above, the result of your query is correct since the underscore is the wildcard for a single character. However, you might want to try the following which basically does the same as your LIKE ... + '%':

SELECT Name
  FROM(SELECT Name
         FROM @Emp
         WHERE LEN(Name) >= LEN(@name)
      ) X
   WHERE SUBSTRING(Name, 1, LEN(@name)) = @name

SQL Like operator not working if underscore at first char

2 Answers2