sql-serverselectunicodecollationnvarchar

SQL LIKE Operator doesn't work with Asian Languages (SQL Server 2008)


I have a SQL Server database column that is of type NVarchar and is filled with standard Persian characters. when I'm trying to run a very simple query on it which incorporates the LIKE operator, the resultset becomes empty although I know the query term is present in the table. Here is the very simple example query which doesn't act correctly:

SELECT * FROM T_Contacts WHERE C_ContactName LIKE '%ف%'

ف is a Persian character and the ContactName column contains multiple entries which contain that character.

Please tell me how should I rewrite the expression or what change should I apply. Note that my database's collation is SQL_Latin1_General_CP1_CI_AS.

Thank you very much


Solution

  • Also, if those values are stored as NVARCHAR (which I hope they are!!), you should always use the N'..' prefix for any string literals to make sure you don't get any unwanted conversions back to non-Unicode VARCHAR.

    So you should be searching:

    SELECT * FROM T_Contacts 
    WHERE C_ContactName COLLATE Persian_100_CI_AS LIKE N'%ف%'