store arabic in SQL database

Question

I tried to store Arabic string in SQL 2008 database but it converted to " question mark " why ? and what should I do ?

Answer 1

In case of Oracle, I can tell you that it is required to setup the database in AL32UTF8 format. So that data gets stored in multibytes. While retrieving that data, locale needs to be set properly to view data in the proper format. In your case, probably database is not in UTF8 format? Once you verify that, make sure your locale is set properly to view that data. Probably get a hex dump of the string you are storing and also get the same hex dump from the database table. Compare the hex values and if they are same, you know that data is going into database properly, its just the locale needs to be set properly at the client side to view the data.

Answer 2

You need to choose an Arabic collation for your varchar/char columns or use Unicode (nchar/nvarchar)

CREATE TABLE #test
(
col1 VARCHAR(100) COLLATE Latin1_General_100_CI_AI,
col2 VARCHAR(100) COLLATE Arabic_CI_AI_KS_WS,
col3 NVARCHAR(100)
)
INSERT INTO #test VALUES(N'لا أتكلم العربية',N'لا أتكلم العربية',N'لا أتكلم العربية')

SELECT * FROM #test

Returns

col1                           col2                           col3
------------------------------ ------------------------------ ------------------------------
?? ????? ???????               لا أتكلم العربية               لا أتكلم العربية

To see a list of Arabic collations use

SELECT name, description 
FROM fn_helpcollations() 
WHERE name LIKE 'Arabic%'