MSSQL searching unicode characthers using LIKE operator

Question

I have a stored procedure where I pass a parameter that is unicode and looks like following:

מוכר שמן קוקוס בכחל מיני ואריציות

Now the problem here is that when I enter something in my form to search for this value in m table like fllowing:

IF LEN(@SearchValue) > 0
        BEGIN
                SET @WhereQuery = @WhereQuery +

                 '(Type='  + CAST(@type AS NVARCHAR(10)) + ' and UserId=' + CAST(@userid AS NVARCHAR(10)) + ') and'
                 + '(convert(nvarchar(max),SentWord) like ''%' + @SearchValue  + '%''  or '
                + 'convert(nvarchar(max),Comment) like ''%' + @SearchValue  + '%'')'
        END

Where @SearchValue is defined as nvarchar(200) in SQL server and table columns that hold the specific value are:

SentWord and Comment and both are unicode defined as nvarchar(600).

What am I doing wrong here? Why cant MSSQL search by hebrew characthers ? Can someone help me out?

As @Jeroen stated , possible fix would be to add N after LIKE operator like following:

  IF LEN(@SearchValue) > 0
            BEGIN
                    SET @WhereQuery = @WhereQuery +

                     '(Type='  + CAST(@type AS NVARCHAR(10)) + ' and UserId=' + CAST(@userid AS NVARCHAR(10)) + ') and'
                     + '(convert(nvarchar(max),SentWord) like N''%' + @SearchValue  + '%''  or '
                    + 'convert(nvarchar(max),Comment) like N''%' + @SearchValue  + '%'')'
            END

But it still doesn't works...

Use `N'Unicode data'` to construct Unicode character literals. Your fix is just two characters away (put them after `LIKE`). — Jeroen Mostert, May 23 '18 at 09:49
Possible duplicate of [How to use 'LIKE' statement with unicode strings?](https://stackoverflow.com/questions/4251765/how-to-use-like-statement-with-unicode-strings) — EzLo, May 23 '18 at 09:51
@JeroenMostert still doesn't works.... :/ I've done it like you mentioned, but still nothing... — User987, May 23 '18 at 15:23

score 1 · Answer 1 · answered May 23 '18 at 09:53

Don't concatenate your strings like that!!! It's an injection nightmare!

Next, your declaring your literal unicode string as a varchar, not an nvarchar. if you try SELECT 'מוכר שמן קוקוס בכחל מיני ואריציות'; notice the return value is '???? ??? ????? ???? ???? ????????'. You need to prefix it with N, thus: SELECT N'מוכר שמן קוקוס בכחל מיני ואריציות';.

Now, the important is parametrising that SQL... Unfortunately I don't have enough of the SQL to actually do this in full for you, so here's a different example instead:

DECLARE @SQL nvarchar(MAX);
DECLARE @string nvarchar(20) = N'Test';

SET @SQL = 'SELECT * FROM MyTable WHERE MyColumn = @dString;'; --Notice the variable in the dynamic SQL

PRINT @SQL;
EXEC sp_executesql @SQL, N'dString nvarchar(20)',@dString = @string; --Notice the variable declaration and assignment.

MSSQL searching unicode characthers using LIKE operator

1 Answers1