50

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

OMG Ponies
  • 314,254
  • 77
  • 507
  • 490
kartal
  • 16,372
  • 32
  • 98
  • 143

9 Answers9

79

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,371
  • 13
  • 25
Martin Smith
  • 419,657
  • 83
  • 708
  • 800
  • 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
17

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
  • 13,431
  • 9
  • 89
  • 88
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,485
  • 1
  • 17
  • 11
6

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

You should put N before string to make it unicode

nazim hatipoglu
  • 624
  • 12
  • 24
3

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

INSERT INTO CompanyMaster values(N'" + txtCompNameAR.Text + "',N'" + txtCompAddressAR.Text + "','" + txtPh.Text + "')
Ahmad
  • 1,278
  • 5
  • 22
  • 39
Pradeepkumar Cb
  • 31
  • 2
3

Add 'N' before every value. example:

INSERT INTO table1 VALUES(N'aaaaaaaaa',N'ששששששששששששש',N'aaaaaaaaaaa',N'ششششششششششش')
Ahmad Shbita
  • 61
  • 5
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

Uttam Ughareja
  • 493
  • 2
  • 9
  • 18
1

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

Biskrem Muhammad
  • 3,270
  • 1
  • 28
  • 34
-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
  • 77,470
  • 19
  • 176
  • 187
Akram
  • 9