53

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

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
kartal
  • 17,436
  • 34
  • 100
  • 145

9 Answers9

84

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%'
fluidguid
  • 1,511
  • 14
  • 25
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 4
    You are AWESOME !! Appreciate it much !! I am going to add your answer to my blog. – UB. Nov 12 '12 at 20:22
  • using Unicode data types like (nchar/nvarchar) was very enough , thank you very much Martin – sh.e.salh Oct 21 '13 at 00:19
  • 2
    I used nvarchar and i still get ?.? output from 'د.إ' – Sam Feb 23 '16 at 05:16
  • 6
    @Sam, You might have written something like UPDATE table SET column = 'العربية', instead of: UPDATE table SET column = N'العربية'... The difference is in the "N" placed before the start of the string to indicate that it is a unicode... That is, you have to specify that the string is unicode in addition to the column being unicode. – Omaer Jun 30 '16 at 20:46
  • Thnks but what about storing these 3 columns on the disk.? How much **One character** in these 3 columns take space on disk? – Arian Dec 31 '19 at 15:40
18

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

the column Data type is nvarchar()

enter image description here

after that I inserted Arabic with no problems

enter image description here

Basheer AL-MOMANI
  • 14,473
  • 9
  • 96
  • 92
8

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
A Ghazal
  • 2,693
  • 1
  • 19
  • 12
6

insert into table (column) values (N'xxx').)

You should put N before string to make it unicode

nazim hatipoglu
  • 634
  • 10
  • 24
5

Add 'N' before every value. example:

INSERT INTO table1 VALUES(N'aaaaaaaaa',N'ששששששששששששש',N'aaaaaaaaaaa',N'ششششششششششش')
Ahmad Shbita
  • 73
  • 1
  • 5
3

Try using this:
the column Data type is nvarchar()

INSERT INTO CompanyMaster values(N'" + txtCompNameAR.Text + "',N'" + txtCompAddressAR.Text + "','" + txtPh.Text + "')
Ahmad
  • 1,618
  • 5
  • 24
  • 46
Pradeepkumar Cb
  • 31
  • 2
2

This is helpful but work here's what works for me in all cases

ALTER DATABASE [database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
GO 

ALTER DATABASE [database] COLLATE ARABIC_CI_AS; 
GO 

ALTER DATABASE [database] SET MULTI_USER; 
GO

Update: eventually I have to change datatype varchar to nvarchar in my project

Dale K
  • 25,246
  • 15
  • 42
  • 71
Uttam Ughareja
  • 842
  • 2
  • 12
  • 21
1

make sure all your tables and varchar columns have the collation of utf8_general_ci

Biskrem Muhammad
  • 4,074
  • 3
  • 31
  • 38
-7

Iti is easy to store Arabic string in Oracle. Use this code:

declare @P_CUSTOMER_NAME  nchar(50) 
set @P_CUSTOMER_NAME2=N'أختبار'

The above will save in Oracle just fine.

Perception
  • 79,279
  • 19
  • 185
  • 195
Akram
  • 9