|
1
2
3
4
5
6
7
8
9
10
11
|
--自然数表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
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
--所有简体中文的排序规则
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
|
|
1
2
3
4
|
SELECT
n,x=
CAST
(n
AS
binary
(2)),u=
NCHAR
(n)
FROM
Nums
WHERE
n
BETWEEN
19968
AND
40869
19968 0x4E00 一
40869 0x9FA5 龥
|
|
1
2
3
4
5
|
SELECT
n,x=
CAST
(n
AS
binary
(2)),uq=
NCHAR
(n),ub=
NCHAR
(n-65248)
FROM
Nums
WHERE
n
BETWEEN
65281
AND
65374
SELECT
NCHAR
(12288),
NCHAR
(32)
65281 0xFF01 ！ !
65374 0xFF5E ～ ~
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
CREATE
FUNCTION
dbo.full2half(
@String nvarchar(
max
)
)
RETURNS
nvarchar(
max
)
AS
/*
全角(Fullwidth)转换为半角(Halfwidth)
*/
BEGIN
DECLARE
@chr
nchar
(1)
DECLARE
@i
int
SET
@String =
REPLACE
(@String,N
' '
,N
' '
)
SET
@i = PATINDEX(N
'%[！-～]%'
COLLATE
Latin1_General_BIN,@String)
WHILE @i > 0
BEGIN
SET
@chr =
SUBSTRING
(@String,@i,1)
SET
@String =
REPLACE
(@String,@chr,
NCHAR
(UNICODE(@chr)-65248))
SET
@i = PATINDEX(N
'%[！-～]%'
COLLATE
Latin1_General_BIN,@String)
END
RETURN
@String
END
GO
CREATE
FUNCTION
dbo.half2full(
@String nvarchar(
max
)
)
RETURNS
nvarchar(
max
)
AS
/*
半角(Halfwidth)转换为全角(Fullwidth)
*/
BEGIN
DECLARE
@chr
nchar
(1)
DECLARE
@i
int
SET
@String =
REPLACE
(@String,N
' '
,N
' '
)
SET
@i = PATINDEX(N
'%[!-~]%'
COLLATE
Latin1_General_BIN,@String)
WHILE @i > 0
BEGIN
SET
@chr =
SUBSTRING
(@String,@i,1)
SET
@String =
REPLACE
(@String,@chr,
NCHAR
(UNICODE(@chr)+65248))
SET
@i = PATINDEX(N
'%[!-~]%'
COLLATE
Latin1_General_BIN,@String)
END
RETURN
@String
END
GO
|
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有