6

Dear Friends, I've faced with a problem never thought of ever. My problem seems too simple but I can't find a solution to it. 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 smiple example query which doesn't act corectly: SELECT * FROM T_Contacts WHERE C_ContactName LIKE '%ف%'

ف is a persian character and the ContactName coulmn 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

Farshid
  • 5,134
  • 9
  • 59
  • 87

2 Answers2

7

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'%ف%'
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
4

Shouldn't it be:

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

ie, with the N in front of the comparing string, so it treats it like an nvarchar?

veljkoz
  • 8,384
  • 8
  • 55
  • 91