SQLServer中文处理
|
本方来自于:http://topic.csdn.net/u/20100720/14/3ad225ec-f731-43c8-9730-149e82bd9186.html ? 按：
--
自然数表1-1M
CREATE
TABLE
Nums(n
int
NOT
NULL
PRIMARY
KEY
CLUSTERED
)
WITH
B1
AS
(
SELECT
n
=
1
UNION
ALL
SELECT
n
=
1
),
--
2
B2
AS
(
SELECT
n
=
1
FROM
B1 a
CROSS
JOIN
B1 b),
--
4
B3
AS
(
SELECT
n
=
1
FROM
B2 a
CROSS
JOIN
B2 b),
--
16
B4
AS
(
SELECT
n
=
1
FROM
B3 a
CROSS
JOIN
B3 b),
--
256
B5
AS
(
SELECT
n
=
1
FROM
B4 a
CROSS
JOIN
B4 b),
--
65536
CTE
AS
(
SELECT
r
=
ROW_NUMBER()
OVER
(
ORDER
BY
(
SELECT
1
))
FROM
B5 a
CROSS
JOIN
B3 b)
--
65536 * 16
INSERT
INTO
Nums(n)
SELECT
TOP
(
1000000
) r
FROM
CTE
ORDER
BY
r
--所有简体中文的排序规则 SELECT * FROM fn_helpcollations() WHERE name LIKE 'Chinese[_]PRC[_]%' --中文系统常用字符 SELECT n,x,u_cias,u_cias_RN = RANK() OVER(ORDER BY u_cias),u_cias_ws,u_cias_ws_RN = RANK() OVER(ORDER BY u_cias_ws),u_stroke,u_stroke_RN = RANK() OVER(ORDER BY u_stroke),u_stroke_ws,u_stroke_ws_RN = RANK() OVER(ORDER BY u_stroke_ws),u_en_cias,u_en_cias_RN = RANK() OVER(ORDER BY u_en_cias),u_en_cias_ws,u_en_cias_ws_RN = RANK() OVER(ORDER BY u_en_cias_ws),u_bin,u_bin_RN = RANK() OVER(ORDER BY u_bin),a_zh_cias,a_zh_cias_RN = RANK() OVER(ORDER BY a_zh_cias),a_zh_cias_ws,a_zh_cias_ws_RN = RANK() OVER(ORDER BY a_zh_cias_ws),a_zh_stroke,a_zh_stroke_RN = RANK() OVER(ORDER BY a_zh_stroke),a_zh_stroke_ws,a_zh_stroke_ws_RN = RANK() OVER(ORDER BY a_zh_stroke_ws),a_zh_bin,a_zh_bin_RN = RANK() OVER(ORDER BY a_zh_bin) FROM ( SELECT n,x = CAST(n AS binary(2)),u_cias = NCHAR(n) COLLATE Chinese_PRC_CI_AS,u_cias_ws = NCHAR(n) COLLATE Chinese_PRC_CI_AS_WS,u_stroke = NCHAR(n) COLLATE Chinese_PRC_Stroke_CI_AS,u_stroke_ws = NCHAR(n) COLLATE Chinese_PRC_Stroke_CI_AS_WS,u_en_cias = NCHAR(n) COLLATE Latin1_General_CI_AS,u_en_cias_ws = NCHAR(n) COLLATE Latin1_General_CI_AS_WS,u_bin = NCHAR(n) COLLATE Chinese_PRC_BIN,--Unicode字符串所有BIN排序都相同，与n和x排序结果一致 a_zh_cias = CAST(NCHAR(n) AS char(2)) COLLATE Chinese_PRC_CI_AS,a_zh_cias_ws = CAST(NCHAR(n) AS char(2)) COLLATE Chinese_PRC_CI_AS_WS,a_zh_stroke = CAST(NCHAR(n) AS char(2)) COLLATE Chinese_PRC_Stroke_CI_AS,a_zh_stroke_ws = CAST(NCHAR(n) AS char(2)) COLLATE Chinese_PRC_Stroke_CI_AS_WS,a_zh_bin = CAST(NCHAR(n) AS char(2)) COLLATE Chinese_PRC_BIN --ANSI相同CodePage的字符串所有BIN排序 都相同 FROM Nums WHERE n BETWEEN 32 AND 126 --ASCII OR n BETWEEN 19968 AND 40869 --中文字符 OR n BETWEEN 65281 AND 65374 --全角标点字母数字，对应半角为n-65248的ASCII字符 OR n = 12288 --全角空格，对应半角空格为32 ) code ORDER BY n二、中文字符相关的匹配 如上面查询所示， 在UCS-2中，19968至40869是中文字符： SQL code
SELECT
n,x
=
CAST
(n
AS
binary
(
2
)),u
=
NCHAR
(n)
FROM
Nums
WHERE
n
BETWEEN
19968
AND
40869
19968
0x4E00
一
40869
0x9FA5
龥
全角标点字母数字的范围是65281至65374，全角空格需要特殊处理： SQL code
SELECT
n,uq
=
NCHAR
(n),ub
=
NCHAR
(n
-
65248
)
FROM
Nums
WHERE
n
BETWEEN
65281
AND
65374
SELECT
NCHAR
(
12288
),
NCHAR
(
32
)
65281
0xFF01
！ !
65374
0xFF5E
～
~
因而，想要匹配一个包含中文字符的字符串可用如下语句： （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |