SQLServer汉字转全拼音函数

最近用到项目，当输入错别字时也需要匹配,直接网上找到一篇直接改写加了拼音排序，让语句更通用。拿来主义

USE Test
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<='                        
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!