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.
LIKE operator fails on embedded space
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?