CODEWITHSUNDEEP
sqlsql-server
Jethij
Jethij

Reputation: 21

SQL Like operator not working if underscore at first char

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

Upvotes: 1

Views: 928

Answers (3)

Pavel Hodek
Pavel Hodek

Reputation: 14577

Wrap underscore using brackets:

CREATE TABLE [Emp] (
    [Name] NVARCHAR(100)
)

INSERT INTO [Emp] ([Name]) VALUES
(N'_#@#_123'),
(N'@#@#@123'),
(N'@#@#_123'),
(N'_#@#_123')

DECLARE @name NVARCHAR(MAX) = '_#@#_1'

--SELECT [Name] FROM [Emp] WHERE [Name] LIKE @name + '%' -- not working as expected
SELECT [Name] FROM [Emp] WHERE [Name] LIKE REPLACE(@name, '_', '[_]') + '%' -- OK

Try working demo

Upvotes: 0

George Menoutis
George Menoutis

Reputation: 7240

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

Upvotes: 3

Tyron78
Tyron78

Reputation: 4187

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

Upvotes: 0

Related Questions