CODEWITHSUNDEEP
sqlsql-serversql-server-2008unicodewhere-clause
User987
User987

Reputation: 3823

MSSQL searching unicode characthers using LIKE operator

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...

Upvotes: 0

Views: 520

Answers (1)

Thom A
Thom A

Reputation: 95561

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.

Upvotes: 1

Related Questions