SQL SERVER 擷取拼音簡稱的函數

標籤：back   upd   create   sub   ascii碼錶   rgb   class   while迴圈   short   

函數：

/*建立取拼音首字母函數*/ create function [dbo].[fn_ChineseToSpell](@strChinese varchar(500)=‘‘) returns varchar(500) as begin /*函數實現開始*/      declare @strLen int,@return varchar(500),@i int      declare @n int,@c char(1),@chn nchar(1)       select @strLen=len(@strChinese),@return=‘‘,@i=0      while @i<@strLen      begin /*while迴圈開始*/             select @i=@i+1,@n=63,@chn=substring(@strChinese,@i,1)              if @chn>‘z‘/*原理:“字串排序以及ASCII碼錶”*/                                 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 ‘丌‘ /*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 ‘帀‘ union all select @chn) as a  order by chn COLLATE Chinese_PRC_CI_AS ) as b               else                 set @c=@chn             set @return=@return+@c       end /*while迴圈結束*/       return(@return)  end /*函數實現結束*/

使用方法：

UPDATE dbo.T_Base_Unit SET ShortName = dbo.fn_ChineseToSpell(Name)

SQL SERVER 擷取拼音簡稱的函數