SQLServer汉字转全拼音函数和声母

最近用到项目,需要用到汉字转拼音和声母,数据库是SqlServer,所以直接在数据库里面做了一下转换。

拿来主义,来源于博客园和CSDN

可以做到输入错别字时也需要匹配,让语句更通用。

USE 数据库名称
go
 
IF OBJECT_ID('Fn_GetQuanPin','Fn') IS NOT NULL
	DROP FUNCTION fn_GetQuanPin
go
 
create function [dbo].[fn_GetQuanPin](@str varchar(100))
returns varchar(8000)
as
begin
 declare @re varchar(8000),@crs varchar(10)
 declare @strlen int 
 select @strlen=len(@str),@re=''
 while @strlen>0
 begin  
  set @crs= substring(@str,@strlen,1)
      select @re=
        CASE 
        when @crs<'吖' COLLATE Chinese_PRC_CS_AS_KS_WS then @crs
        when @crs<='厑' COLLATE Chinese_PRC_CS_AS_KS_WS then 'A'
        when @crs<='靉' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ai'
        when @crs<='黯' COLLATE Chinese_PRC_CS_AS_KS_WS then 'An'
        when @crs<='醠' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ang'
        when @crs<='驁' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ao'
        when @crs<='欛' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ba'
        when @crs<='瓸' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bai'
        when @crs<='瓣' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ban'
        when @crs<='鎊' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bang'
        when @crs<='鑤' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bao'
        when @crs<='鐾' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bei'
        when @crs<='輽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ben'
        when @crs<='鏰' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Beng'
        when @crs<='鼊' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bi'
        when @crs<='變' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bian'
        when @crs<='鰾' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Biao'
        when @crs<='彆' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bie'
        when @crs<='鬢' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bin'
        when @crs<='靐' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bing'
        when @crs<='蔔' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bo'
        when @crs<='簿' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bu'
        when @crs<='囃' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ca'
        when @crs<='乲' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cai'
        when @crs<='爘' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Can'
        when @crs<='賶' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cang'
        when @crs<='鼜' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cao'
        when @crs<='簎' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ce'
        when @crs<='笒' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cen'
        when @crs<='乽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ceng'
        when @crs<='詫' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cha'
        when @crs<='囆' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chai'
        when @crs<='顫' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chan'
        when @crs<='韔' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chang'
        when @crs<='觘' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chao'
        when @crs<='爡' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Che'
        when @crs<='讖' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chen'
        when @crs<='秤' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cheng'
        when @crs<='鷘' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chi'
        when @crs<='銃' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chong'
        when @crs<='殠' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chou'
        when @crs<='矗' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chu'
        when @crs<='踹' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chuai'
        when @crs<='鶨' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chuan'
        when @crs<='愴' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chuang'
        when @crs<='顀' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chui'
        when @crs<='蠢' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chun'
        when @crs<='縒' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chuo'
        when @crs<='嗭' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ci'
        when @crs<='謥' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cong'
        when @crs<='輳' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cou'
        when @crs<='顣' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cu'
        when @crs<='爨' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cuan'
        when @crs<='臎' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cui'
        when @crs<='籿' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cun'
        when @crs<='錯' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cuo'
        when @crs<='橽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Da'
        when @crs<='靆' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dai'
        when @crs<='饏' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dan'
        when @crs<='闣' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dang'
        when @crs<='纛' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dao'
        when @crs<='的' COLLATE Chinese_PRC_CS_AS_KS_WS then 'De'
        when @crs<='扽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Den'
        when @crs<='鐙' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Deng'
        when @crs<='螮' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Di'
        when @crs<='嗲' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dia'
        when @crs<='驔' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dian'
        when @crs<='鑃' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Diao'
        when @crs<='嚸' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Die'
        when @crs<='顁' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ding'
        when @crs<='銩' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Diu'
        when @crs<='霘' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dong'
        when @crs<='鬭' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dou'
        when @crs<='蠹' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Du'
        when @crs<='叾' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Duan'
        when @crs<='譵' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dui'
        when @crs<='踲' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dun'
        when @crs<='鵽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Duo'
        when @crs<='鱷' COLLATE Chinese_PRC_CS_AS_KS_WS then 'E'
        when @crs<='摁' COLLATE Chinese_PRC_CS_AS_KS_WS then 'En'
        when @crs<='鞥' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Eng'
        when @crs<='樲' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Er'
        when @crs<='髮' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fa'
        when @crs<='瀪' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fan'
        when @crs<='放' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fang'
        when @crs<='靅' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fei'
        when @crs<='鱝' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fen'
        when @crs<='覅' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Feng'
        when @crs<='梻' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fo'
        when @crs<='鴀' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fou'
        when @crs<='猤' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fu'
        when @crs<='魀' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ga'
        when @crs<='瓂' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gai'
        when @crs<='灨' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gan'
        when @crs<='戇' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gang'
        when @crs<='鋯' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gao'
        when @crs<='獦' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ge'
        when @crs<='給' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gei'
        when @crs<='搄' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gen'
        when @crs<='堩' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Geng'
        when @crs<='兣' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gong'
        when @crs<='購' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gou'
        when @crs<='顧' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gu'
        when @crs<='詿' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gua'
        when @crs<='恠' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Guai'
        when @crs<='鱹' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Guan'
        when @crs<='撗' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Guang'
        when @crs<='鱥' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gui'
        when @crs<='謴' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gun'
        when @crs<='腂' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Guo'
        when @crs<='哈' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ha'
        when @crs<='饚' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hai'
        when @crs<='鶾' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Han'
        when @crs<='沆' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hang'
        when @crs<='兞' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hao'
        when @crs<='靏' COLLATE Chinese_PRC_CS_AS_KS_WS then 'He'
        when @crs<='嬒' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hei'
        when @crs<='恨' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hen'
        when @crs<='堼' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Heng'
        when @crs<='鬨' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hong'
        when @crs<='鱟' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hou'
        when @crs<='鸌' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hu'
        when @crs<='蘳' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hua'
        when @crs<='蘾' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Huai'
        when @crs<='鰀' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Huan'
        when @crs<='鎤' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Huang'
        when @crs<='顪' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hui'
        when @crs<='諢' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hun'
        when @crs<='夻' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Huo'
        when @crs<='驥' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ji'
        when @crs<='嗧' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jia'
        when @crs<='鑳' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jian'
        when @crs<='謽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jiang'
        when @crs<='釂' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jiao'
        when @crs<='繲' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jie'
        when @crs<='齽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jin'
        when @crs<='竸' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jing'
        when @crs<='蘔' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jiong'
        when @crs<='欍' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jiu'
        when @crs<='爠' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ju'
        when @crs<='羂' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Juan'
        when @crs<='钁' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jue'
        when @crs<='攈' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jun'
        when @crs<='鉲' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ka'
        when @crs<='乫' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Kai'
        when @crs<='矙' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Kan'
        when @crs<='閌' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Kang'
        when @crs<='鯌' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Kao'
        when @crs<='騍' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ke'
        when @crs<='褃' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ken'
        when @crs<='鏗' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Keng'
        when @crs<='廤' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Kong'
        when @crs<='鷇' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Kou'
        when @crs<='嚳' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ku'
        when @crs<='骻' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Kua'
        when @crs<='鱠' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Kuai'
        when @crs<='窾' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Kuan'
        when @crs<='鑛' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Kuang'
        when @crs<='鑎' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Kui'
        when @crs<='睏' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Kun'
        when @crs<='穒' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Kuo'
        when @crs<='鞡' COLLATE Chinese_PRC_CS_AS_KS_WS then 'La'
        when @crs<='籟' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Lai'
        when @crs<='糷' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Lan'
        when @crs<='唥' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Lang'
        when @crs<='軂' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Lao'
        when @crs<='餎' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Le'
        when @crs<='脷' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Lei'
        when @crs<='睖' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Leng'
        when @crs<='瓈' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Li'
        when @crs<='倆' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Lia'
        when @crs<='纞' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Lian'
        when @crs<='鍄' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Liang'
        when @crs<='瞭' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Liao'
        when @crs<='鱲' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Lie'
        when @crs<='轥' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Lin'
        when @crs<='炩' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ling'
        when @crs<='咯' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Liu'
        when @crs<='贚' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Long'
        when @crs<='鏤' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Lou'
        when @crs<='氇' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Lu'
        when @crs<='鑢' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Lv'
        when @crs<='亂' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Luan'
        when @crs<='擽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Lue'
        when @crs<='論' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Lun'
        when @crs<='鱳' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Luo'
        when @crs<='嘛' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ma'
        when @crs<='霢' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Mai'
        when @crs<='蘰' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Man'
        when @crs<='蠎' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Mang'
        when @crs<='唜' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Mao'
        when @crs<='癦' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Me'
        when @crs<='嚜' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Mei'
        when @crs<='們' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Men'
        when @crs<='霥' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Meng'
        when @crs<='羃' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Mi'
        when @crs<='麵' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Mian'
        when @crs<='廟' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Miao'
        when @crs<='鱴' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Mie'
        when @crs<='鰵' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Min'
        when @crs<='詺' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ming'
        when @crs<='謬' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Miu'
        when @crs<='耱' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Mo'
        when @crs<='麰' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Mou'
        when @crs<='旀' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Mu'
        when @crs<='魶' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Na'
        when @crs<='錼' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nai'
        when @crs<='婻' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nan'
        when @crs<='齉' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nang'
        when @crs<='臑' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nao'
        when @crs<='呢' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ne'
        when @crs<='焾' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nei'
        when @crs<='嫩' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nen'
        when @crs<='能' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Neng'
        when @crs<='嬺' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ni'
        when @crs<='艌' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nian'
        when @crs<='釀' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Niang'
        when @crs<='脲' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Niao'
        when @crs<='钀' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nie'
        when @crs<='拰' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nin'
        when @crs<='濘' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ning'
        when @crs<='靵' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Niu'
        when @crs<='齈' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nong'
        when @crs<='譳' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nou'
        when @crs<='搙' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nu'
        when @crs<='衄' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nv'
        when @crs<='瘧' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nue'
        when @crs<='燶' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nuan'
        when @crs<='桛' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Nuo'
        when @crs<='鞰' COLLATE Chinese_PRC_CS_AS_KS_WS then 'O'
        when @crs<='漚' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ou'
        when @crs<='袙' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Pa'
        when @crs<='磗' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Pai'
        when @crs<='鑻' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Pan'
        when @crs<='胖' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Pang'
        when @crs<='礮' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Pao'
        when @crs<='轡' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Pei'
        when @crs<='喯' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Pen'
        when @crs<='喸' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Peng'
        when @crs<='鸊' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Pi'
        when @crs<='騙' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Pian'
        when @crs<='慓' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Piao'
        when @crs<='嫳' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Pie'
        when @crs<='聘' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Pin'
        when @crs<='蘋' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ping'
        when @crs<='魄' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Po'
        when @crs<='哛' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Pou'
        when @crs<='曝' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Pu'
        when @crs<='蟿' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Qi'
        when @crs<='髂' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Qia'
        when @crs<='縴' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Qian'
        when @crs<='瓩' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Qiang'
        when @crs<='躈' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Qiao'
        when @crs<='籡' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Qie'
        when @crs<='藽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Qin'
        when @crs<='櫦' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Qing'
        when @crs<='瓗' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Qiong'
        when @crs<='糗' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Qiu'
        when @crs<='覻' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Qu'
        when @crs<='勸' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Quan'
        when @crs<='礭' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Que'
        when @crs<='囕' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Qun'
        when @crs<='橪' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ran'
        when @crs<='讓' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Rang'
        when @crs<='繞' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Rao'
        when @crs<='熱' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Re'
        when @crs<='餁' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ren'
        when @crs<='陾' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Reng'
        when @crs<='馹' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ri'
        when @crs<='穃' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Rong'
        when @crs<='嶿' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Rou'
        when @crs<='擩' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ru'
        when @crs<='礝' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ruan'
        when @crs<='壡' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Rui'
        when @crs<='橍' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Run'
        when @crs<='鶸' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ruo'
        when @crs<='栍' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Sa'
        when @crs<='虄' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Sai'
        when @crs<='閐' COLLATE Chinese_PRC_CS_AS_KS_WS then 'San'
        when @crs<='喪' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Sang'
        when @crs<='髞' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Sao'
        when @crs<='飋' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Se'
        when @crs<='篸' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Sen'
        when @crs<='縇' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Seng'
        when @crs<='霎' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Sha'
        when @crs<='曬' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Shai'
        when @crs<='鱔' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Shan'
        when @crs<='緔' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Shang'
        when @crs<='潲' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Shao'
        when @crs<='欇' COLLATE Chinese_PRC_CS_AS_KS_WS then 'She'
        when @crs<='瘮' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Shen'
        when @crs<='賸' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Sheng'
        when @crs<='瓧' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Shi'
        when @crs<='鏉' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Shou'
        when @crs<='虪' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Shu'
        when @crs<='誜' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Shua'
        when @crs<='卛' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Shuai'
        when @crs<='腨' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Shuan'
        when @crs<='灀' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Shuang'
        when @crs<='睡' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Shui'
        when @crs<='鬊' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Shun'
        when @crs<='鑠' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Shuo'
        when @crs<='乺' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Si'
        when @crs<='鎹' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Song'
        when @crs<='瘶' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Sou'
        when @crs<='鷫' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Su'
        when @crs<='算' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Suan'
        when @crs<='鐩' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Sui'
        when @crs<='潠' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Sun'
        when @crs<='蜶' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Suo'
        when @crs<='襨' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ta'
        when @crs<='燤' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Tai'
        when @crs<='賧' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Tan'
        when @crs<='燙' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Tang'
        when @crs<='畓' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Tao'
        when @crs<='蟘' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Te'
        when @crs<='朰' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Teng'
        when @crs<='趯' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ti'
        when @crs<='舚' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Tian'
        when @crs<='糶' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Tiao'
        when @crs<='餮' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Tie'
        when @crs<='乭' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ting'
        when @crs<='憅' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Tong'
        when @crs<='透' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Tou'
        when @crs<='鵵' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Tu'
        when @crs<='褖' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Tuan'
        when @crs<='駾' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Tui'
        when @crs<='坉' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Tun'
        when @crs<='籜' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Tuo'
        when @crs<='韤' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Wa'
        when @crs<='顡' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Wai'
        when @crs<='贎' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Wan'
        when @crs<='朢' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Wang'
        when @crs<='躛' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Wei'
        when @crs<='璺' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Wen'
        when @crs<='齆' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Weng'
        when @crs<='齷' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Wo'
        when @crs<='鶩' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Wu'
        when @crs<='衋' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Xi'
        when @crs<='鏬' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Xia'
        when @crs<='鼸' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Xian'
        when @crs<='鱌' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Xiang'
        when @crs<='斆' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Xiao'
        when @crs<='躞' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Xie'
        when @crs<='釁' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Xin'
        when @crs<='臖' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Xing'
        when @crs<='敻' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Xiong'
        when @crs<='齅' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Xiu'
        when @crs<='蓿' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Xu'
        when @crs<='贙' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Xuan'
        when @crs<='瀥' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Xue'
        when @crs<='鑂' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Xun'
        when @crs<='齾' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ya'
        when @crs<='灩' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Yan'
        when @crs<='樣' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Yang'
        when @crs<='鑰' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Yao'
        when @crs<='岃' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ye'
        when @crs<='齸' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Yi'
        when @crs<='檼' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Yin'
        when @crs<='譍' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ying'
        when @crs<='喲' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Yo'
        when @crs<='醟' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Yong'
        when @crs<='鼬' COLLATE Chinese_PRC_CS_AS_KS_WS then 'You'
        when @crs<='爩' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Yu'
        when @crs<='願' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Yuan'
        when @crs<='鸙' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Yue'
        when @crs<='韻' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Yun'
        when @crs<='雥' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Za'
        when @crs<='縡' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zai'
        when @crs<='饡' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zan'
        when @crs<='臟' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zang'
        when @crs<='竈' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zao'
        when @crs<='稄' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ze'
        when @crs<='鱡' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zei'
        when @crs<='囎' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zen' 
        when @crs<='贈' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zeng'
        when @crs<='醡' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zha'
        when @crs<='瘵' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhai'
        when @crs<='驏' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhan'
        when @crs<='瞕' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhang'
        when @crs<='羄' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhao'
        when @crs<='鷓' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhe'
        when @crs<='黮' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhen'
        when @crs<='證' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zheng'
        when @crs<='豒' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhi'
        when @crs<='諥' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhong'
        when @crs<='驟' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhou'
        when @crs<='鑄' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhu'
        when @crs<='爪' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhua'
        when @crs<='跩' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhuai'
        when @crs<='籑' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhuan'
        when @crs<='戅' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhuang'
        when @crs<='鑆' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhui'
        when @crs<='稕' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhun'
        when @crs<='籱' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zhuo'
        when @crs<='漬' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zi'
        when @crs<='縱' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zong'
        when @crs<='媰' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zou'
        when @crs<='謯' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zu'
        when @crs<='攥' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zuan'
        when @crs<='欈' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zui'
        when @crs<='銌' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zun'
        when @crs<='咗' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Zuo'
        else  @crs end+@re,@strlen=@strlen-1 
   end
 return(@re)
END
GO
SELECT dbo.fn_GetQuanPin('拿来主义')

原文:https://blog.csdn.net/roy_88/article/details/8772631

下面是汉字转声母

一、该函数传入字符串,返回数据为:如果为汉字字符,返回该字符的首字母,如果为非汉字字符,则返回本身。

二、用到的知识点:汉字对应的UNICODE值,汉字的排序规则。

CREATE FUNCTION GetInitialLetter(@ChineseString NVARCHAR(4000)) 
RETURNS NVARCHAR(4000) 
AS
BEGIN
 
DECLARE @SingleCharacter NCHAR(1),
@ReturnString NVARCHAR(4000) 
SET @ReturnString = '' 

WHILE LEN(@ChineseString)>0 
BEGIN

--依次取单个字符
SET @SingleCharacter = LEFT(@ChineseString,1) 

----汉字字符,返回字符对应首字母,非汉字字符,返回原字符
IF(UNICODE(@SingleCharacter) BETWEEN 19968 AND 19968+20901)
SET @ReturnString = @ReturnString + 
(SELECT TOP 1 PY FROM
(SELECT 'A' AS PY,N'骜' AS ChineseCharacters 
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'咗' 
)SpellingTable 
where ChineseCharacters > = @SingleCharacter COLLATE Chinese_PRC_CS_AS_KS_WS 
ORDER by PY ASC)
ELSE
SET @ReturnString = @ReturnString + @SingleCharacter

 
SET @ChineseString = RIGHT(@ChineseString,LEN(@ChineseString)-1) 

END
 
RETURN @ReturnString 

END
GO

来源:https://www.cnblogs.com/waitingAlone/p/5441469.html

发表评论

电子邮件地址不会被公开。 必填项已用*标注