SQL Server obtains the complete set of Chinese Character initials function version 4-[Leaf]

-- Create the create function [DBO] to take the first letter of a Chinese character (the third edition). [f_getpy_v3] (@ Col varchar (1000) returns varchar (1000) as begin declare @ Cyc int, @ Len int, @ SQL varchar (1000), @ char varbinary (20) select @ Cyc = 1, @ Len = Len (@ col), @ SQL = ''while @ Cyc <= @ Len begin select @ char = cast (substring (@ Col, @ cyc, 1) as varbinary) Declare @ MACO table (bcode varbinary (20), ecode varbinary (20), letter varchar (10) insert into @ macoselect 0xb0 A1, 0xb0c4, 'A' Union allselect 0xb0c5, 0xb2c0, 'B' Union allselect 0xb2c1, 0xb4ed, 'C' Union allselect 0xb4ee, 0xb6e9, 'D' Union allselect 0xb6ea, 0xb7a1, 'E' Union allselect 0xb7a2, 0xb8c0, 'F' Union allselect 0xb8c1, 0xb9fd, 'G' Union allselect 0xb9fe, 0xbbf6, 'H' Union allselect 0xbbf7, 0xbfa5, 'J' Union allselect 0xbfa6, 0xc0ab, 'K' Union allselect 0xc0ac, 0xc2e7, 'L' Union allselect 0xc2e8, 0xc4c2, 'M' Union allsel ECT 0xc4c3, 0xc5b5, 'n' Union allselect 0xc5b6, 0xc5bd, 'O' Union allselect 0xc5be, 0xc6d9, 'P' Union allselect 0xc6da, 0xc8ba, 'q' Union allselect 0xc8bb, 0xc8f5, 'R' Union allselect 0xc8f6, 0xcbf9,'s 'Union allselect 0 xcbfa, 0xcdd9, 't'union allselect 0 xcdda, 0xcef3, 'w' Union allselect 0xcef4, 0xd1b8, 'X' Union allselect 0xd1b9, 0xd4d0, 'y' Union allselect 0xd4d1, 0xd7f9, 'z' select top 1 @ SQL = @ SQL + letter from @ Ma CO where @ char between bcode and ecode set @ Cyc = @ Cyc + 1 end return @ SQL endgo -- create the CREATE FUNCTION [DBO] for getting the first letter of a Chinese character (Fourth Edition). [f_getpy_v4] (@ Col varchar (1000) returns varchar (1000) Begin declare @ Cyc int, @ Len int, @ SQL varchar (1000), @ char varbinary (20) select @ Cyc = 1, @ Len = Len (@ col), @ SQL = ''while @ Cyc <= @ Len begin select @ char = cast (substring (@ Col, @ cyc, 1) as varbinary) If @ char >=0xb0a1 and @ char <= 0x B0c4 set @ SQL = @ SQL + 'A' else if @ char> = 0xb0c5 and @ char <= 0xb2c0 set @ SQL = @ SQL + 'B' else if @ char> = 0xb2c1 and @ char <= 0xb4ed set @ SQL = @ SQL + 'C' else if @ char> = 0xb4ee and @ char <= 0xb6e9 set @ SQL = @ SQL + 'D' else if @ char> = 0xb6ea and @ char <= 0xb7a1 set @ SQL = @ SQL + 'E' else if @ char> = 0xb7a2 and @ char <= 0xb8c0 set @ SQL = @ SQL + 'F' else if @ char> = 0xb8c1 and @ char <= 0xb9fd set @ SQL = @ SQL + 'G' else if @ char> = 0xb9fe and @ char <= 0xb Bf6 set @ SQL = @ SQL + 'H' else if @ char> = 0xbbf7 and @ char <= 0xbfa5 set @ SQL = @ SQL + 'J' else if @ char> = 0xbfa6 and @ char <= 0xc0ab set @ SQL = @ SQL + 'K' else if @ char> = 0xc0ac and @ char <= 0xc2e7 set @ SQL = @ SQL + 'l' else if @ char> = 0xc2e8 and @ char <= 0xc4c2 set @ SQL = @ SQL + 'M' else if @ char> = 0xc4c3 and @ char <= 0xc5b5 set @ SQL = @ SQL + 'n' else if @ char> = 0xc5b6 and @ char <= 0xc5bd set @ SQL = @ SQL + 'O' else if @ char> = 0xc5be and @ char <= 0xc6 D9 set @ SQL = @ SQL + 'P' else if @ char> = 0xc6da and @ char <= 0xc8ba set @ SQL = @ SQL + 'q' else if @ char> = 0xc8bb and @ char <= 0xc8f5 set @ SQL = @ SQL + 'R' else if @ char> = 0xc8f6 and @ char <= 0xcbf9 set @ SQL = @ SQL +'s' else if @ char> = 0 xcbfa and @ char <= 0xcdd9 set @ SQL = @ SQL + 't'else if @ char> = 0 xcdda and @ char <= 0xcef3 set @ SQL = @ SQL + 'W' else if @ char> = 0xcef4 and @ char <= 0xd1b8 set @ SQL = @ SQL + 'X' else if @ char> = 0xd1b9 and @ char <= 0xd4d 0 set @ SQL = @ SQL + 'y' else if @ char> = 0xd4d1 and @ char <= 0xd7f9 set @ SQL = @ SQL + 'Z' set @ Cyc = @ Cyc + 1 end return @ SQL endgo -- create a function for retrieving the first letter of a Chinese character (first version) create Function [DBO]. [f_getpy_v1] (@ STR nvarchar (4000) returns nvarchar (4000) asbegin declare @ word nchar (1), @ py nvarchar (4000) set @ py = ''while Len (@ Str)> 0 begin set @ word = left (@ STR, 1) set @ py = @ py + (case when Unicode (@ word) between 19968 and 19968 + 20901 then (S Elect top 1 py from (select 'A' as py, n' then 'as word Union all select' B ', n' 'Union all select 'C ', N 'hour' Union all select 'D', N 'hour' Union all select 'E', N 'hour' Union all select 'F ', N 'hour' Union all select 'G', N 'hour' Union all select 'h', N 'hour' Union all select 'J ', N 'hour' Union all select 'k', N 'hangzhou' Union all select 'l', N 'hangzhou' Union all select 'M ', N 'hour' Union all select 'n', N 'hangzhou' Union all select 'O', N 'hangzhou' Union all Select 'P', n' exposure 'Union all select 'Q', n' comment 'Union all select 'R', n' comment 'Union all select s ', N 'hour' Union all select 'T', N 'hangzhou' Union all select 'w', N 'hangzhou' Union all select 'x ', N 'delimiter' Union all select 'y', n' then 'Union all select 'Z', n' then ') t where word >=@ word collate chinese_prc_cs_as_ks_ws order by Py ASC) else @ word end) set @ STR = right (@ STR, Len (@ Str)-1) end return @ pyendgo -- create a function for getting the first letter of a Chinese character (version 2) Create Function [d Bo]. [f_getpy_v2] (@ STR varchar (500) = '') returns varchar (500) asbegin declare @ strlen int, @ return varchar (500), @ II int declare @ n int, @ C char (1), @ CHN nchar (1) Select @ strlen = Len (@ Str), @ return = '', @ II = 0 set @ II = 0 while @ II <@ strlen begin select @ II = @ II + 1, @ n = 63, @ CHN = substring (@ STR, @ II, 1) If @ CHN> 'Z' select @ n = @ n + 1, @ C = case CHN when @ CHN then Char (@ n) else @ C end from (select top 27 * from (select ch N = 'ay' Union all select '8' Union all select 'all' except 'Union all select' before 'Union all select' 'Union all select ''Union all select '-- because have no' I 'Union all select' doesn't 'Union all select' else 'Union all select 'distinct' Union all select 'distinct' Union all select' Oh 'Union all select' else 'Union all select '7' Union all select' else 'Union all Sel ECT 'except 'Union all select' he 'Union all select 'hour' -- no 'U' Union all select 'hour' -- no 'V' Union all select 'hour' Union all select 'Xi 'Union all select' 'Union all select 'hour' Union all select @ CHN) as a order by CHN collate chinese_prc_ci_as) as B else set @ C = 'A' set @ return = @ return + @ C end return (@ return) end-the idea is basically the same, but different types lead to different efficiency. The effects tested in different environments are different. Select DBO. f_getpy_v1 ('I am a native Chinese') Select DBO. f_getpy_v2 ('I am a native Chinese') Select DBO. f_getpy_v3 ('I am a native Chinese') Select DBO. f_getpy_v4 ('I am a native China') -- The percentage of Overhead I have tested is: -- 1: 2: 3: 4 corresponds to 18%: 38%: 44%: 0% -- if you are interested, you can test it locally to see the execution plan. Which of the four functions is the most efficient? You can leave the overhead percentage below. Thank you!