SQL LIKE wildcard returning rows without match

vx6bjr1n 于 2023-02-28 发布在其他

关注(0)|答案(1)|浏览(139)

We have a table that's used for logging. It has two columns:

queueid (int)
message (nvarchar(MAX))

I am looking for rows with a specific userid (seven digits) in the message (output for the log).

When I run

SELECT queueid, message
FROM  [dbname].[dbo].[log]
WHERE
  [dbname].[dbo].[log].[message] LIKE '%1597498%';

SSMS returns 105 rows. However most of the values for message do not contain that string. If I select the message column in the results and paste the text into an editor, there are less than 50 matches for that string. I get this result with other user id values as well.

I thought the LIKE syntax was fairly straightforward. Is there another issue that might be at play?

sql-server

来源：https://stackoverflow.com/questions/75560221/sql-like-wildcard-returning-rows-without-match

1条答案

按热度按时间

sgtfey8w1#

SQL Server doesn't lie - it's not going to return a row that doesn't match the predicate you've specified.

SSMS may hide the truth, though, since its grid and text displays limit the amount of text that will show.

So, it's likely that the problem is just that the data is there in the table but not in the subset of the value that is shown in SSMS.

To see that the data is there, just return that portion:

;WITH src AS
(
  SELECT queueid, message, pos = patindex('%1597498%',message)
  FROM dbo.[log]
  WHERE message LIKE N'%1597498%'
)
SELECT queueid, 
  SUBSTRING(message, CASE WHEN pos > 10 THEN pos - 10 ELSE 1 END, 50),
  message
FROM src;

Working example in this db<>fiddle .

赞(0）回复(0）举报 2023-02-28

我来回答

SQL LIKE wildcard returning rows without match

1条答案

相关问题

热门标签

最新问答