從網絡上收刮了一些，以備后用

?

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 begindeclare @cyc int,@length int,@str1 varchar(100),@charcate varbinary(20)set @cyc=1--從第幾個字開始取set @length=len(@str)--輸入漢字的長度set @str1=''--用于存放返回值while @cyc<=@lengthbegin select @charcate=cast(substring(@str,@cyc,1) as varbinary)--每次取出一個字并將其轉變成二進制,便于與GBK編碼表進行比較if @charcate>=0XB0A1 and @charcate<=0XB0C4set @str1=@str1+'A'--說明此漢字的首字母為A,以下同上else if @charcate>=0XB0C5 and @charcate<=0XB2C0set @str1=@str1+'B'else if @charcate>=0XB2C1 and @charcate<=0XB4EDset @str1=@str1+'C'else if @charcate>=0XB4EE and @charcate<=0XB6E9set @str1=@str1+'D'else if @charcate>=0XB6EA and @charcate<=0XB7A1set @str1=@str1+'E'else if @charcate>=0XB7A2 and @charcate<=0XB8C0set @str1=@str1+'F'else if @charcate>=0XB8C1 and @charcate<=0XB9FDset @str1=@str1+'G'else if @charcate>=0XB9FE and @charcate<=0XBBF6set @str1=@str1+'H'else if @charcate>=0XBBF7 and @charcate<=0XBFA5set @str1=@str1+'J'else if @charcate>=0XBFA6 and @charcate<=0XC0ABset @str1=@str1+'K'else if @charcate>=0XC0AC and @charcate<=0XC2E7set @str1=@str1+'L'else if @charcate>=0XC2E8 and @charcate<=0XC4C2set @str1=@str1+'M'else if @charcate>=0XC4C3 and @charcate<=0XC5B5set @str1=@str1+'N'else if @charcate>=0XC5B6 and @charcate<=0XC5BDset @str1=@str1+'O'else if @charcate>=0XC5BE and @charcate<=0XC6D9set @str1=@str1+'P'else if @charcate>=0XC6DA and @charcate<=0XC8BAset @str1=@str1+'Q'else if @charcate>=0XC8BB and @charcate<=0XC8F5set @str1=@str1+'R'else if @charcate>=0XC8F6 and @charcate<=0XCBF9set @str1=@str1+'S'else if @charcate>=0XCBFA and @charcate<=0XCDD9set @str1=@str1+'T'else if @charcate>=0XCDDA and @charcate<=0XCEF3set @str1=@str1+'W'else if @charcate>=0XCEF4 and @charcate<=0XD1B8set @str1=@str1+'X'else if @charcate>=0XD1B9 and @charcate<=0XD4D0set @str1=@str1+'Y'else if @charcate>=0XD4D1 and @charcate<=0XD7F9set @str1=@str1+'Z'set @cyc=@cyc+1--取出輸入漢字的下一個字endreturn @str1--返回輸入漢字的首字母end --測試數據 --select dbo.GetPY('從來就是這樣酷,我酷,就是酷,看你能把我怎么樣,哈哈') ------------------------------------------------------------------

?

轉載于:https://www.cnblogs.com/liangdaye/p/3570650.html

總結

以上是生活随笔為你收集整理的sql 汉字转首字母拼音的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。