LIKE operator fails on embedded space

JAL 591 Reputation points
2021-01-01T09:37:18.61+00:00

Our customer recently insisted on allowing special chars in our DB. So I created one test record with this ProductID (inserted from a spreadsheet).

Declare @p nvarchar(500) = N'ValidChars-`~!@#$%(,)-_{}[]\|?,/,><.: 012345abcABC;'

And to escape any wildcard chars I wrote this function:

Alter Function dbo.fnEscapeWildcards(
@Steinar nvarchar(max)
)
Returns nvarchar(max)
AS
Begin
if @Steinar IS NULL Return NULL;
Set @Steinar = Replace(@Steinar , '[', '[[]');
Set @Steinar = Replace(@Steinar , '%', '[%]');
Set @Steinar = Replace(@Steinar , '', '[]');
Return @Steinar
End

And yet the following query does not return the record.

Set @p = dbo.fnEscapeWildcards(@p)
Select * from Products where ProductName LIKE '%' + @p + '%'

That query succeeds on any substring except those containing the space - the space is just before the zero. I used the Ascii function to verify that the DB really is storing that char as a space:

Select Ascii(Right(productName, 14)) from Products where RowID = 54; -- Returns 32.

The wildcard chars are not the problem. Like I said, it works on any substring (with or without wildcards), unless the substring includes the space.

What am I doing wrong?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,651 questions
0 comments No comments
{count} votes

Accepted answer
  1. JAL 591 Reputation points
    2021-01-01T10:04:55.73+00:00

    Nevermind. I was copying and pasting from the web applicaton and somehow, due to word-wrap, the browser lost a space. It's actually a double-space before the zero, as far as the DB is concerned.

    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.