sql 汉字转首字母拼音
从网络上收刮了一些，以备后用
create function fun_getPY(@str nvarchar(4000)) returns nvarchar(4000) as begin 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 (select top 1 PY from ( select 'A' as PY,N'驁' as word union all select 'B',N'簿' union all select 'C',N'錯' union all select 'D',N'鵽' union all select 'E',N'樲' union all select 'F',N'鰒' union all select 'G',N'腂' union all select 'H',N'夻' union all select 'J',N'攈' union all select 'K',N'穒' union all select 'L',N'鱳' union all select 'M',N'旀' union all select 'N',N'桛' union all select 'O',N'漚' union all select 'P',N'曝' union all select 'Q',N'囕' union all select 'R',N'鶸' union all select 'S',N'蜶' union all select 'T',N'籜' union all select 'W',N'鶩' union all select 'X',N'鑂' union all select 'Y',N'韻' union all select 'Z',N'咗' ) 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 @PY end --函数调用实例： --select dbo.fun_getPY('中华人民共和国') ----------------------------------------------------------------- --可支持大字符集20000个汉字! create function f_ch2py(@chn nchar(1)) returns char(1) as begin declare @n int declare @c char(1) set @n = 63 select @n = @n +1, @c = case chn when @chn then char(@n) else @c end from( select top 27 * from ( select chn = '吖' union all select '八' union all select '嚓' union all select '咑' union all select '妸' union all select '发' union all select '旮' union all select '铪' union all select '丌' union all select --because have no 'i' '丌' union all select '咔' union all select '垃' union all select '嘸' union all select '拏' union all select '噢' union all select '妑' union all select '七' union all select '呥' union all select '仨' union all select '他' union all select '屲' union all select --no 'u' '屲' union all select --no 'v' '屲' union all select '夕' union all select '丫' union all select '帀' union all select @chn) as a order by chn COLLATE Chinese_PRC_CI_AS ) as b return(@c) end go select dbo.f_ch2py('中') --Z select dbo.f_ch2py('国') --G select dbo.f_ch2py('人') --R select dbo.f_ch2py('镆') --M go -----------------调用 CREATE FUNCTION F_GetHelpCode ( @cName VARCHAR(20) ) RETURNS VARCHAR(12) AS BEGIN DECLARE @i SMALLINT, @L SMALLINT , @cHelpCode VARCHAR(12), @e VARCHAR(12), @iAscii SMALLINT SELECT @i=1, @L=0 , @cHelpCode='' while @L<=12 AND @i<=LEN(@cName) BEGIN SELECT @e=LOWER(SUBSTRING(@cname,@i,1)) SELECT @iAscii=ASCII(@e) IF @iAscii>=48 AND @iAscii <=57 OR @iAscii>=97 AND @iAscii <=122 or @iAscii=95 SELECT @cHelpCode=@cHelpCode +@e ELSE IF @iAscii>=176 AND @iAscii <=247 SELECT @cHelpCode=@cHelpCode + dbo.f_ch2py(@e) ELSE SELECT @L=@L-1 SELECT @i=@i+1, @L=@L+1 END RETURN @cHelpCode END GO --调用 select dbo.F_GetHelpCode('大力') ------------------------------------------------ /* 函数名称:GetPY 实现功能:将一串汉字输入返回每个汉字的拼音首字母 如输入-->'经营承包责任制'-->输出-->JYCBZRZ. 完成时间:2005-09-18 作者:郝瑞军 参数:@str-->是你想得到拼音首字母的汉字 返回值:汉字的拼音首字母 */ create function GetPY(@str varchar(500)) returns varchar(500) as begin declare @cyc int,@length int,@str1 varchar(100),@charcate varbinary(20) set @cyc=1--从第几个字开始取 set @length=len(@str)--输入汉字的长度 set @str1=''--用于存放返回值 while @cyc<=@length begin select @charcate=cast(substring(@str,@cyc,1) as varbinary)--每次取出一个字并将其转变成二进制,便于与GBK编码表进行比较 if @charcate>=0XB0A1 and @charcate<=0XB0C4 set @str1=@str1+'A'--说明此汉字的首字母为A,以下同上 else if @charcate>=0XB0C5 and @charcate<=0XB2C0 set @str1=@str1+'B' else if @charcate>=0XB2C1 and @charcate<=0XB4ED set @str1=@str1+'C' else if @charcate>=0XB4EE and @charcate<=0XB6E9 set @str1=@str1+'D' else if @charcate>=0XB6EA and @charcate<=0XB7A1 set @str1=@str1+'E' else if @charcate>=0XB7A2 and @charcate<=0XB8C0 set @str1=@str1+'F' else if @charcate>=0XB8C1 and @charcate<=0XB9FD set @str1=@str1+'G' else if @charcate>=0XB9FE and @charcate<=0XBBF6 set @str1=@str1+'H' else if @charcate>=0XBBF7 and @charcate<=0XBFA5 set @str1=@str1+'J' else if @charcate>=0XBFA6 and @charcate<=0XC0AB set @str1=@str1+'K' else if @charcate>=0XC0AC and @charcate<=0XC2E7 set @str1=@str1+'L' else if @charcate>=0XC2E8 and @charcate<=0XC4C2 set @str1=@str1+'M' else if @charcate>=0XC4C3 and @charcate<=0XC5B5 set @str1=@str1+'N' else if @charcate>=0XC5B6 and @charcate<=0XC5BD set @str1=@str1+'O' else if @charcate>=0XC5BE and @charcate<=0XC6D9 set @str1=@str1+'P' else if @charcate>=0XC6DA and @charcate<=0XC8BA set @str1=@str1+'Q' else if @charcate>=0XC8BB and @charcate<=0XC8F5 set @str1=@str1+'R' else if @charcate>=0XC8F6 and @charcate<=0XCBF9 set @str1=@str1+'S' else if @charcate>=0XCBFA and @charcate<=0XCDD9 set @str1=@str1+'T' else if @charcate>=0XCDDA and @charcate<=0XCEF3 set @str1=@str1+'W' else if @charcate>=0XCEF4 and @charcate<=0XD1B8 set @str1=@str1+'X' else if @charcate>=0XD1B9 and @charcate<=0XD4D0 set @str1=@str1+'Y' else if @charcate>=0XD4D1 and @charcate<=0XD7F9 set @str1=@str1+'Z' set @cyc=@cyc+1--取出输入汉字的下一个字 end return @str1--返回输入汉字的首字母 end --测试数据 --select dbo.GetPY('从来就是这样酷,我酷,就是酷,看你能把我怎么样,哈哈') ------------------------------------------------------------------
