1 2 3 4 5 6 7 8 9 10 | --自然数表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 | --所有简体中文的排序规则 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 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 | 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 |
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 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | CREATE FUNCTION dbo.ucs2_to_utf8( @ucs2 varbinary( max ) ) RETURNS varbinary( max ) AS /* U-00000000 ... U-0000007F 0xxxxxxx U-00000080 ... U-000007FF 110xxxxx 10xxxxxx U-00000800 ... U-0000FFFF 1110xxxx 10xxxxxx 10xxxxxx */ BEGIN DECLARE @ output varbinary( max ), @i int , @code int SET @ output = 0x SET @i = 1 WHILE 1 = 1 BEGIN SET @code = CAST ( SUBSTRING (@ucs2,@i+1,1) + SUBSTRING (@ucs2,@i,1) AS int ) IF @code = 0 BREAK IF @code >= 0x0800 SET @ output = @ output + CAST (@code / 4096 + 224 AS binary (1)) + CAST ((@code % 4096) / 64 + 128 AS binary (1)) + CAST ((@code % 4096) % 64 + 128 AS binary (1)) ELSE IF @code >= 0x0080 SET @ output = @ output + CAST (@code / 64 + 192 AS binary (1)) + CAST (@code % 64 + 128 AS binary (1)) ELSE SET @ output = @ output + CAST (@code AS binary (1)) SET @i = @i + 2 END RETURN @ output END GO CREATE FUNCTION dbo.utf8_to_ucs2( @utf8 varbinary( max ) ) RETURNS varbinary( max ) AS BEGIN DECLARE @ output varbinary( max ), @i int , @ next int , @code int , @tmp varbinary(1) SET @ output = 0x SET @i = 1 SET @ next = 0 WHILE 1 = 1 BEGIN SET @tmp = SUBSTRING (@utf8,@i,1) IF @tmp = 0x BREAK IF @tmp BETWEEN 0x01 AND 0x7F SET @ output = @ output + @tmp + 0x00 ELSE IF @tmp BETWEEN 0xC0 AND 0xDF BEGIN SET @code = ( CAST (@tmp AS int ) & 0x1F) * 64 SET @ next = 1 END ELSE IF @tmp BETWEEN 0xE0 AND 0xEF BEGIN SET @code = ( CAST (@tmp AS int ) & 0x0F) * 4096 SET @ next = 2 END ELSE IF @tmp BETWEEN 0x80 AND 0xBF AND @ next IN (1,2) BEGIN IF @ next = 1 BEGIN SET @code = @code + ( CAST (@tmp AS int ) & 0x3F) SET @ output = @ output + CAST ( NCHAR (@code) AS binary (2)) END IF @ next = 2 SET @code = @code + ( CAST (@tmp AS int ) & 0x3F) * 64 SET @ next = @ next - 1 END ELSE RETURN NULL SET @i = @i + 1 END RETURN @ output END GO |
联系客服