2

We are currently having a problem loading arabic into a SQL Server 2008 database.

We want to load
بِسْمِ اللَّهِ الرَّحْمَٰنِ الرَّحِيمِ

but in the database it changes to

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

with a ? added,

We have set the collation Arabic_CI_AI_KS_WS and SQL_Latin1_General_CP1256_CS_AS

and neither of these work..... any suggestions?

migrated from stackoverflow.com Oct 9 '11 at 4:51

This question came from our site for professional and enthusiast programmers.

  • Thanks, I have tried an insert statement, and even a copy and paste into the database, but as you move away from the cell it adds the ? – user665782 Oct 8 '11 at 20:48
  • Hi Martin, thanks for the suggestion, we have tried the unicode column but still no luck... – user665782 Oct 8 '11 at 20:58
  • Perfect, that works.. we had the Arabic as VarChar doh... appreciate your time. – user665782 Oct 8 '11 at 21:13
  • 1
    @MartinSmith: make it as an answer, please, so that user665782 could mark it as needed. – Marian Oct 9 '11 at 8:19
  • How do i mark it as an answer... sorry for the noob question – user665782 Oct 10 '11 at 19:32
3

From a quick test this end none of the built in collations can represent all characters in that string in a single byte code page so you would need to use a unicode datatype.

SET NOCOUNT ON;

DECLARE @StringToTest nvarchar(2047) = N'بِسْمِ اللَّهِ الرَّحْمَٰنِ الرَّحِيمِ'

--The below without the problematic character returns 7 results
--DECLARE @StringToTest nvarchar(2047) = N'بِسْمِ اللَّهِ الرَّحْمَنِ الرَّحِيمِ'


CREATE TABLE #Results
(
name sysname
)

CREATE TABLE #Chars(
C NCHAR(1)
)
INSERT INTO #Chars
SELECT DISTINCT SUBSTRING(@StringToTest,number,1)
FROM master..spt_values
WHERE type='P' AND number BETWEEN 1 AND LEN(@StringToTest)


CREATE TABLE #Collations
(
     code TINYINT PRIMARY KEY
);

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),   --2
        E02(N) AS (SELECT 1 FROM E00 a, E00 b), --4
        E04(N) AS (SELECT 1 FROM E02 a, E02 b), --16
        E08(N) AS (SELECT 1 FROM E04 a, E04 b) --256
INSERT INTO #Collations
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1
FROM E08    

DECLARE @AlterScript NVARCHAR(MAX) = ''

SELECT @AlterScript = @AlterScript + ' 
RAISERROR(''Processing' + name + ''',0,1) WITH NOWAIT;
ALTER TABLE #Collations ADD ' + name + ' CHAR(1) COLLATE ' + name + ';
EXEC(''UPDATE #Collations SET ' + name + '=CAST(code AS BINARY(1))'');
EXEC(''UPDATE #Collations SET ' + name + '=NULL WHERE ASCII(' + name + ') <> code'');
EXEC(''
IF NOT EXISTS(
SELECT *
FROM #Chars
WHERE NOT EXISTS(SELECT * FROM #Collations WHERE ' + name + ' = C COLLATE ' + name + '))
INSERT INTO #Results
SELECT ''''' + name + '''''
'');
'
FROM   sys.fn_helpcollations()
WHERE  name LIKE '%CS_AS'
       AND name NOT IN    /*Unicode Only Collations*/
                        ( 'Assamese_100_CS_AS', 'Bengali_100_CS_AS',
                         'Divehi_90_CS_AS', 'Divehi_100_CS_AS' ,
                         'Indic_General_90_CS_AS', 'Indic_General_100_CS_AS',
                             'Khmer_100_CS_AS', 'Lao_100_CS_AS',
                         'Maltese_100_CS_AS', 'Maori_100_CS_AS',
                         'Nepali_100_CS_AS', 'Pashto_100_CS_AS',
                         'Syriac_90_CS_AS', 'Syriac_100_CS_AS',
                         'Tibetan_100_CS_AS' )


EXEC (@AlterScript)


SELECT * FROM #Results

DROP TABLE #Collations
DROP TABLE #Results
DROP TABLE #Chars
1

I use utf8_bin to store Arabic in MySQL. If you have that option for MSSQL I would try using that. You should also make sure that your application isn't the cause of the offending character.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.