The keyword "LIKE" does not work in SQL Server 2008

Question

I have bulk data in a SQL-Server table. One of the fields contains the following data:

'(اے انسان!) کیا تو نہیں جانتا)' 

I tried:

 SELECT * from Ayyat where Data like '%انسان%' ; 

but he does not show the result.

Answer 1

Plese uses N before the string if the language is not English:

  SELECT * from Ayyat where Data like N'%انسان%' ; 

Answer 2

If you store Urdu, Arabic or a language other than English in your database, you must first convert your database to a different format, and then to a table.

first you need to convert the database encoding and also map the new database

 ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci 

then convert the table

 ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci 

after that, run a regular query

 SELECT * FROM table_name WHERE Datas LIKE '%انسان%' 

Note: if you do not convert your database and table of other languages, and special characters will be replaced by question marks .