How to store Arabic in SQL server?

Question

I am trying to store Arabic-strings in my database. it is working fine by using COLLATE Arabic_CI_AI_KS_WS but some of Arabic records are missing some Arabic-alphabets. i have tried with some other sollate with but result as same. how to fix it ? table structure :

CREATE TABLE [dbo].[Ayyat_Translation_Language_old_20131209] (
    [Ayat_Translation_Language_ID] INT            IDENTITY (1, 1) NOT NULL,
    [Translation_Laanguage_ID]     INT            NULL,
    [Juz_ID]                       INT            NULL,
    [Surah_ID]                     INT            NOT NULL,
    [Ayat_Description]             VARCHAR (2000) COLLATE Arabic_CI_AI_KS_WS NOT NULL
)

insertion code :

 string query = "insert into Ayyat_Translation_Language_old_20131209 values(null,null," + surah + ",N'" + verse + "')"; where verse contains Arabic contents.

and it stores data like this (with question-marks) :

?بِسْمِ اللَّهِ الرَّحْمَ?نِ الرَّحِيمِ

i have read that link : store arabic in SQL database

Mitch Wheat · Accepted Answer · 2013-12-27T10:41:56.660

7

To store unicode string data, use NVARCHAR(2000) rather than VARCHAR(2000) for column [Ayat_Description]

Ref.: nchar and nvarchar

edited Dec 27 '13 at 10:41

answered Dec 27 '13 at 08:48

Mitch Wheat

295,962
43
465
541

score 3 · Answer 2 · answered Aug 09 '16 at 08:33

3

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 even with Tashkeel

this in SQl server management studio 2012

answered Aug 09 '16 at 08:33

Basheer AL-MOMANI

14,473
9
96
92

score 1 · Answer 3 · answered Dec 27 '13 at 08:55

It may help

CREATE TABLE [dbo].[Ayyat_Translation_Language_old_20131209] (
    [Ayat_Translation_Language_ID] INT            IDENTITY (1, 1) NOT NULL,
    [Translation_Laanguage_ID]     INT            NULL,
    [Juz_ID]                       INT            NULL,
    [Surah_ID]                     INT            NOT NULL,
    [Ayat_Description]             NVARCHAR (2000) COLLATE Arabic_CI_AI_KS_WS NOT NULL

score 0 · Answer 4 · edited Dec 27 '13 at 09:02

0

use arabic collation or use unicode(nvarchar(max))

This is a Example table For your answer :

CREATE TABLE #t11
        (

        column1 NVARCHAR(100) 

        )
        INSERT INTO #t11 VALUES(N'لا أتكلم العربية')

        SELECT * FROM #t11

fiddle demo

edited Dec 27 '13 at 09:02

answered Dec 27 '13 at 08:51

vhadalgi

7,027
6
38
67

How to store Arabic in SQL server?

4 Answers4

fiddle demo