SQL LIKE statement does not work with Asian languages ​​(SQL Server 2008)

Question

Dear friends, I ran into a problem that I never thought about. My problem seems too simple, but I can not find a solution. I have a SQL Server database column that is of type NVarchar and is filled with standard Persian characters. when I try to run a very simple query on it that includes the LIKE statement, the result set becomes empty, although I know that the query term is present in the table. Here is an example of a query with a chunk that does not act differently: SELECT * FROM T_Contacts WHERE C_ContactName LIKE '% ف%'

ف is a Persian character, and the counmn ContactName contains several records that contain this character.

Please tell me how to rewrite the expression or what change I should apply. Please note that my database sort is SQL_Latin1_General_CP1_CI_AS.

Many thanks

Answer 1

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

So you should look for:

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

Answer 2

Must not be:

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

those. with Nbefore the comparison string, so it treats it like nvarchar?