Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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

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

2  
How are you loading this? You might well need the N prefix N'بِسْمِ اللَّهِ الرَّحْمَٰنِ الرَّحِيمِ' –  Martin Smith Oct 8 '11 at 20:46
    
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
    
Ah right. Just tried with the N prefix and that didn't help. There was still a ? hidden in there. Maybe you'll need to use a unicode column type –  Martin Smith Oct 8 '11 at 20:55
2  
DECLARE @T TABLE(C NVARCHAR(50)) INSERT INTO @T SELECT N'بِسْمِ اللَّهِ الرَّحْمَٰنِ الرَّحِيمِ' SELECT * FROM @T works fine for me. No ? in the output I can see. –  Martin Smith Oct 8 '11 at 21:08
1  
@MartinSmith: make it as an answer, please, so that user665782 could mark it as needed. –  Marian Oct 9 '11 at 8:19

2 Answers 2

up vote 3 down vote accepted

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
share|improve this answer
    
thanks! Nice answer, btw! –  Marian Oct 9 '11 at 11:08
    
Could you explain a bit what this script does? (Also, you need a space after "Processing".) –  Nick Chammas Oct 19 '11 at 17:41
1  
@Nick - Good catch on the space! Actually it probably does quite a bit more than strictly necessary. I had previously written some code to see the characters supported in all the collation families here and I just re-purposed it for this question. There may well be simpler ways of running through all the collations to test which ones won't mangle any of the characters in a string. –  Martin Smith Oct 19 '11 at 17:48

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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