question

Joe 1 1 avatar image
Joe 1 1 asked

SQL Server Unicode Text Search

Hi Everyone -

I am working on a bi-lingual site where I have to store both English and Non-English characters in a SQL Server 2008 database.

So far I have been able to enter non-english characters (data) and retrieve/display them the same way they were entered in my site.

However, I am facing a challenge when trying to perform search using like or even = operators.

I am passing the search string the same way I capture it for the save operation and performing the search in a stored procedure using the code below which returns everything instead of just the one I am looking for.

Can anyone please point me what I am missing here? The "NAME_AMHARIC" column is of type NVARCHAR.

Thanks in advance for your help!

ALTER PROCEDURE [dbo].[sp_search_products]                    
                    
        @NAME_AMHARIC nvarchar(200) = null                    
                    
    AS BEGIN                    
                    
    SELECT product_id,p.category_id,name,name_amharic                    
                    
      FROM products p                    
      WHERE ltrim(rtrim(p.name_amharic)) = ltrim(rtrim(@NAME_amharic)) AND @NAME_AMHARIC is not null)                    
        ;                    
                    
    END                      
sql-server-2008full-textlikeunicode
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

How did you get this to compile with the extraneous ")" after the null? Another note, trim name_amharic before it is inserted into the products table since the optimizer will not use an index when you have it wrapped with ltrim/rtrim in the WHERE clause
0 Likes 0 ·
Oh the extra ")" was left out when I copied the code from my stored procedure (I removed other tables and joins and select criteria). Does what I am doing make sense though? Any idea why the criteria wouldn't work? I am already triming the source column and input search parameter.
0 Likes 0 ·
The above code looks like it should work, which makes me think the other criteria that you have omitted may be causing all rows to be returned
0 Likes 0 ·
It looks like I am getting better result when I do the matching in varbinary ... where name_amhbin = cast(@Name_amharic as varbinary(max)) Now I am not sure how to apply LIKE operator in binary format. Does anyone has any thoughts?
0 Likes 0 ·

1 Answer

· Write an Answer
abenezer avatar image
abenezer answered
I am trying to search Amahric name from Unicode column in sql2008. As far as i know this works. COLLATE SQL_Latin1_General_Cp437_BIN using like select * from VMembershipFull where FirstName like N'%ይ%' COLLATE SQL_Latin1_General_Cp437_BIN
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.