SQL Server 2008 排序規則阿拉伯語

我們目前在將阿拉伯語載入到 SQL Server 2008 數據庫時遇到問題。

我們想

載入

但在數據庫中它更改為

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

有一個？添加，

我們已經設置了排序規則Arabic_CI_AI_KS_WS和SQL_Latin1_General_CP1256_CS_AS

這些都不起作用……有什麼建議嗎？

從快速測試來看，沒有任何內置排序規則可以在單個字節程式碼頁中表示該字元串中的所有字元，因此您需要使用 unicode 數據類型。

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

引用自：https://dba.stackexchange.com/questions/6627