sql存储过程获取汉字拼音头字母函数的方法
本篇内容介绍了“sql存储过程获取汉字拼音头字母函数的方法”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
复制代码 代码如下:
--函数CREATEfunctionfn_GetPy(@strnvarchar(4000))returnsnvarchar(4000)--WITHENCRYPTIONasbegindeclare@intLenintdeclare@strRetnvarchar(4000)declare@tempnvarchar(100)set@intLen=len(@str)set@strRet=''while@intLen>0beginset@temp=''select@temp=casewhensubstring(@str,@intLen,1)>='帀'then'Z'whensubstring(@str,@intLen,1)>='丫'then'Y'whensubstring(@str,@intLen,1)>='夕'then'X'whensubstring(@str,@intLen,1)>='屲'then'W'whensubstring(@str,@intLen,1)>='他'then'T'whensubstring(@str,@intLen,1)>='仨'then'S'whensubstring(@str,@intLen,1)>='呥'then'R'whensubstring(@str,@intLen,1)>='七'then'Q'whensubstring(@str,@intLen,1)>='妑'then'P'whensubstring(@str,@intLen,1)>='噢'then'O'whensubstring(@str,@intLen,1)>='拏'then'N'whensubstring(@str,@intLen,1)>='嘸'then'M'whensubstring(@str,@intLen,1)>='垃'then'L'whensubstring(@str,@intLen,1)>='咔'then'K'whensubstring(@str,@intLen,1)>='丌'then'J'whensubstring(@str,@intLen,1)>='铪'then'H'whensubstring(@str,@intLen,1)>='旮'then'G'whensubstring(@str,@intLen,1)>='发'then'F'whensubstring(@str,@intLen,1)>='妸'then'E'whensubstring(@str,@intLen,1)>='咑'then'D'whensubstring(@str,@intLen,1)>='嚓'then'C'whensubstring(@str,@intLen,1)>='八'then'B'whensubstring(@str,@intLen,1)>='吖'then'A'elsertrim(ltrim(substring(@str,@intLen,1)))end--对于汉字特殊字符,不生成拼音码if(ascii(@temp)>127)set@temp=''--对于英文中小括号,不生成拼音码if@temp='('or@temp=')'set@temp=''select@strRet=@temp+@strRetset@intLen=@intLen-1endreturnlower(@strRet)endgo--调用selectdbo.fn_getpy('张三')--返回:zs答!:2:取汉字拼音首字母的存储过程Createfunctionfun_getPY(@strnvarchar(4000))returnsnvarchar(4000)asbegindeclare@wordnchar(1),@PYnvarchar(4000)set@PY=''whilelen(@str)>0beginset@word=left(@str,1)--如果非汉字字符,返回原字符set@PY=@PY+(casewhenunicode(@word)between19968and19968+20901then(selecttop1PYfrom(select'A'asPY,N'驁'aswordunionallselect'B',N'簿'unionallselect'C',N'錯'unionallselect'D',N'鵽'unionallselect'E',N'樲'unionallselect'F',N'鰒'unionallselect'G',N'腂'unionallselect'H',N'夻'unionallselect'J',N'攈'unionallselect'K',N'穒'unionallselect'L',N'鱳'unionallselect'M',N'旀'unionallselect'N',N'桛'unionallselect'O',N'漚'unionallselect'P',N'曝'unionallselect'Q',N'囕'unionallselect'R',N'鶸'unionallselect'S',N'蜶'unionallselect'T',N'籜'unionallselect'W',N'鶩'unionallselect'X',N'鑂'unionallselect'Y',N'韻'unionallselect'Z',N'咗')Twhereword>=@wordcollateChinese_PRC_CS_AS_KS_WSorderbyPYASC)else@wordend)set@str=right(@str,len(@str)-1)endreturn@PYend
“sql存储过程获取汉字拼音头字母函数的方法”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。