store arabic in SQL database

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

Solution 1:

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'لا أتكلم العربية')

Note the N before values in insert statement above. If you do not mention it, system will treat the values as Varchar, not NVarchar.

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%'

Solution 2:

All of what you have to do is to make sure that

the column Data type is nvarchar()

after that I inserted Arabic with no problems

Solution 3:

You can change the collation on the database level instead of changing for each column in the database:

USE master;
GO
ALTER DATABASE TestDB
COLLATE Arabic_CI_AI;
GO