SQL Server SQL Like operator not working if underscore at first char

mjqavswn 于 2023-10-15 发布在其他

关注(0)|答案(3)|浏览(102)

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

sql-server

来源：https://stackoverflow.com/questions/66998116/sql-like-operator-not-working-if-underscore-at-first-char

3条答案

按热度按时间

w8rqjzmb1#

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

赞(0）回复(0）举报 2023-10-15

2jcobegt2#

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

赞(0）回复(0）举报 2023-10-15

p8h8hvxi3#

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

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server SQL Like operator not working if underscore at first char

3条答案

相关问题

热门标签

最新问答