problem with WHERE clause matching on Arabic string

Question

I have a SQL Server 2005 database in which I have some tables contain Arabic text. The datatype for those fields is NVARCHAR(n).

The Arabic text inside the table is appearing properly, and when selecting, they appear properly.

Th problem is that searching for Arabic text results in 0 rows.

select * from table_name
where name='arabic_text'

This retrieves no rows, where there is a name with this value.

Can you please guide me on how to write the query?

Solution 1

Try adding N before the text e.g.

select * from table_name
where name=N'arabic_text'

Solution 2

select * from table_name
where name like N'%arabic_text%' 

will work here

Solution 3

If you search for word احمد and your records have أحمد your query will return nothing even if you are using like '%احمد%' . It is better to use Full-Text search.

DECLARE @SearchWord nvarchar(100)
SET @SearchWord = 'احمد'

DECLARE @SearchString nvarchar(100)
SET @SearchString = 'FormsOf(INFLECTIONAL, "' + @SearchWord + '")' 
--SET @SearchString =  @SearchWord 

SELECT Ar.[SuraID]
      ,Ar.[VerseID]
      ,Ar.[AyahText] as Arabic
      ,En.[AyahText] as English
  FROM [QuranDb].[dbo].[ArabicQuran] Ar
  join EnglishQuran En on En.ID=Ar.ID
  where   CONTAINS(ar.AyahText, @SearchString) 
 or  CONTAINS(En.AyahText, @SearchString ) 

Query with Full-Text (Inflectional)

Here is other example using CONTAINSTABLE CONTAINSTABLE link

DECLARE @SearchWord nvarchar(100)
SET @SearchWord = 'لا اله الا الله '

DECLARE @SearchString nvarchar(100)
SET @SearchString ='ISABOUT ("'+ @SearchWord+'")' 

SELECT K.RANK, Ar.AyahText,Ya.AyahText
FROM [ArabicQuran] AS Ar  
  INNER JOIN  
  CONTAINSTABLE([ArabicQuran], AyahText, @SearchString, LANGUAGE N'arabic'  ) AS K  
  ON Ar.ID = K.[KEY] 
  join EnQuranYusufAli Ya on ya.ID=Ar.ID
  order by k.RANK desc 

Comments

[OMG Ponies]

The need to prefix a test comparison with "N" outside the single quotes is because of the data type (NVARCHAR), when searching by non-ASCII characters (which is why you wouldn't notice an issue searching for English text. The N stands for "Nationalized", but means UTF (8 I assume).

[mohammed sameeh]

there is an other problem with sqldatareader when the condtion in sql command is arabic like this select *from users where nam=N 'arabic text' this does not retrieve any data although there is a user name with this arabic value so what is solution

[Conrad Frix]

@Mohammed you're probably not going to get an answer to your .net question when its a comment inside a sql question. You're better off asking another question. Of course doing a search first is a good idea

[mohammed sameeh]

i searched for this question on google but no answer , so i will ask another question ,thank for advice ^_^

[Qasim Bataineh]

i am still looking for an answer to this