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?

Answer 1

Try adding N before the text e.g.

select * from table_name
where name=N'arabic_text'

Answer 2

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

will work here

Answer 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