/*

 根据汉字获取全拼

 1.生成所有读音临时表

 2.根据Chinese_PRC_CS_AS_KS_WS 排序获取读音

*/

CREATE function f_GetPying(@str varchar(100))

returns varchar(8000)

as

begin

 declare @re varchar(8000)

 --生成临时表

 declare @t table(chr nchar(1) collate  Chinese_PRC_CS_AS_KS_WS,py nvarchar(20))

 insert into @t select'吖','a'

 insert into @t select'厑','aes'

 insert into @t select'哎','ai'

 insert into @t select'安','an'

 insert into @t select'肮','ang'

 insert into @t select'凹','ao'

 insert into @t select'八','ba'

 insert into @t select'挀','bai'

 insert into @t select'兡','baike'

 insert into @t select'瓸','baiwa'

 insert into @t select'扳','ban'

 insert into @t select'邦','bang'

 insert into @t select'勹','bao'

 insert into @t select'萡','be'

 insert into @t select'陂','bei'

 insert into @t select'奔','ben'

 insert into @t select'伻','beng'

 insert into @t select'皀','bi'

 insert into @t select'边','bian'

 insert into @t select'辪','uu'

 insert into @t select'灬','biao'

 insert into @t select'憋','bie'

 insert into @t select'汃','bin'

 insert into @t select'冫','bing'

 insert into @t select'癶','bo'

 insert into @t select'峬','bu'

 insert into @t select'嚓','ca'

 insert into @t select'偲','cai'

 insert into @t select'乲','cal'

 insert into @t select'参','can'

 insert into @t select'仓','cang'

 insert into @t select'撡','cao'

 insert into @t select'冊','ce'

 insert into @t select'膥','cen'

 insert into @t select'噌','ceng'

 insert into @t select'硛','ceok'

 insert into @t select'岾','ceom'

 insert into @t select'猠','ceon'

 insert into @t select'乽','ceor'

 insert into @t select'叉','cha'

 insert into @t select'犲','chai'

 insert into @t select'辿','chan'

 insert into @t select'伥','chang'

 insert into @t select'抄','chao'

 insert into @t select'车','che'

 insert into @t select'抻','chen'

 insert into @t select'阷','cheng'

 insert into @t select'吃','chi'

 insert into @t select'充','chong'

 insert into @t select'抽','chou'

 insert into @t select'出','chu'

 insert into @t select'膗','chuai'

 insert into @t select'巛','chuan'

 insert into @t select'刅','chuang'

 insert into @t select'吹','chui'

 insert into @t select'旾','chun'

 insert into @t select'踔','chuo'

 insert into @t select'呲','ci'

 insert into @t select'嗭','cis'

 insert into @t select'从','cong'

 insert into @t select'凑','cou'

 insert into @t select'粗','cu'

 insert into @t select'汆','cuan'

 insert into @t select'崔','cui'

 insert into @t select'邨','cun'

 insert into @t select'瑳','cuo'

 insert into @t select'撮','chua'

 insert into @t select'咑','da'

 insert into @t select'呔','dai'

 insert into @t select'丹','dan'

 insert into @t select'当','dang'

 insert into @t select'刀','dao'

 insert into @t select'恴','de'

 insert into @t select'揼','dem'

 insert into @t select'扥','den'

 insert into @t select'灯','deng'

 insert into @t select'仾','di'

 insert into @t select'嗲','dia'

 insert into @t select'敁','dian'

 insert into @t select'刁','diao'

 insert into @t select'爹','die'

 insert into @t select'哋','dei'

 insert into @t select'嚸','dim'

 insert into @t select'丁','ding'

 insert into @t select'丟','diu'

 insert into @t select'东','dong'

 insert into @t select'吺','dou'

 insert into @t select'剢','du'

 insert into @t select'耑','duan'

 insert into @t select'叾','dug'

 insert into @t select'垖','dui'

 insert into @t select'吨','dun'

 insert into @t select'咄','duo'

 insert into @t select'妸','e'

 insert into @t select'奀','en'

 insert into @t select'鞥','eng'

 insert into @t select'仒','eo'

 insert into @t select'乻','eol'

 insert into @t select'旕','eos'

 insert into @t select'儿','er'

 insert into @t select'发','fa'

 insert into @t select'帆','fan'

 insert into @t select'匚','fang'

 insert into @t select'飞','fei'

 insert into @t select'吩','fen'

 insert into @t select'丰','feng'

 insert into @t select'瓰','fenwa'

 insert into @t select'覅','fiao'

 insert into @t select'仏','fo'

 insert into @t select'垺','fou'

 insert into @t select'夫','fu'

 insert into @t select'猤','fui'

 insert into @t select'旮','ga'

 insert into @t select'侅','gai'

 insert into @t select'甘','gan'

 insert into @t select'冈','gang'

 insert into @t select'皋','gao'

 insert into @t select'戈','ge'

 insert into @t select'给','gei'

 insert into @t select'根','gen'

 insert into @t select'更','geng'

 insert into @t select'啹','geu'

 insert into @t select'喼','gib'

 insert into @t select'嗰','go'

 insert into @t select'工','gong'

 insert into @t select'兝','gongfen'

 insert into @t select'兣','gongli'

 insert into @t select'勾','gou'

 insert into @t select'估','gu'

 insert into @t select'瓜','gua'

 insert into @t select'乖','guai'

 insert into @t select'关','guan'

 insert into @t select'光','guang'

 insert into @t select'归','gui'

 insert into @t select'丨','gun'

 insert into @t select'呙','guo'

 insert into @t select'妎','ha'

 insert into @t select'咍','hai'

 insert into @t select'乤','hal'

 insert into @t select'兯','han'

 insert into @t select'魧','hang'

 insert into @t select'茠','hao'

 insert into @t select'兞','haoke'

 insert into @t select'诃','he'

 insert into @t select'黒','hei'

 insert into @t select'拫','hen'

 insert into @t select'亨','heng'

 insert into @t select'囍','heui'

 insert into @t select'乊','ho'

 insert into @t select'乥','hol'

 insert into @t select'叿','hong'

 insert into @t select'齁','hou'

 insert into @t select'乎','hu'

 insert into @t select'花','hua'

 insert into @t select'徊','huai'

 insert into @t select'欢','huan'

 insert into @t select'巟','huang'

 insert into @t select'灰','hui'

 insert into @t select'昏','hun'

 insert into @t select'吙','huo'

 insert into @t select'嚿','geo'

 insert into @t select'夻','hwa'

 insert into @t select'丌','ji'

 insert into @t select'加','jia'

 insert into @t select'嗧','jialun'

 insert into @t select'戋','jian'

 insert into @t select'江','jiang'

 insert into @t select'艽','jiao'

 insert into @t select'阶','jie'

 insert into @t select'巾','jin'

 insert into @t select'坕','jing'

 insert into @t select'冂','jiong'

 insert into @t select'丩','jiu'

 insert into @t select'欍','jou'

 insert into @t select'凥','ju'

 insert into @t select'姢','juan'

 insert into @t select'噘','jue'

 insert into @t select'军','jun'

 insert into @t select'咔','ka'

 insert into @t select'开','kai'

 insert into @t select'乫','kal'

 insert into @t select'刊','kan'

 insert into @t select'冚','hem'

 insert into @t select'砊','kang'

 insert into @t select'尻','kao'

 insert into @t select'坷','ke'

 insert into @t select'肎','ken'

 insert into @t select'劥','keng'

 insert into @t select'巪','keo'

 insert into @t select'乬','keol'

 insert into @t select'唟','keos'

 insert into @t select'厼','keum'

 insert into @t select'怾','ki'

 insert into @t select'空','kong'

 insert into @t select'廤','kos'

 insert into @t select'抠','kou'

 insert into @t select'扝','ku'

 insert into @t select'夸','kua'

 insert into @t select'蒯','kuai'

 insert into @t select'宽','kuan'

 insert into @t select'匡','kuang'

 insert into @t select'亏','kui'

 insert into @t select'坤','kun'

 insert into @t select'拡','kuo'

 insert into @t select'穒','kweok'

 insert into @t select'垃','la'

 insert into @t select'来','lai'

 insert into @t select'兰','lan'

 insert into @t select'啷','lang'

 insert into @t select'捞','lao'

 insert into @t select'仂','le'

 insert into @t select'雷','lei'

 insert into @t select'塄','leng'

 insert into @t select'唎','li'

 insert into @t select'俩','lia'

 insert into @t select'嫾','lian'

 insert into @t select'簗','liang'

 insert into @t select'蹽','liao'

 insert into @t select'毟','lie'

 insert into @t select'厸','lin'

 insert into @t select'伶','ling'

 insert into @t select'溜','liu'

 insert into @t select'瓼','liwa'

 insert into @t select'囖','lo'

 insert into @t select'龙','long'

 insert into @t select'娄','lou'

 insert into @t select'噜','lu'

 insert into @t select'驴','lv'

 insert into @t select'寽','lue'

 insert into @t select'孪','luan'

 insert into @t select'掄','lun'

 insert into @t select'頱','luo'

 insert into @t select'呣','m'

 insert into @t select'妈','ma'

 insert into @t select'遤','hweong'

 insert into @t select'埋','mai'

 insert into @t select'颟','man'

 insert into @t select'牤','mang'

 insert into @t select'匁','mangmi'

 insert into @t select'猫','mao'

 insert into @t select'唜','mas'

 insert into @t select'庅','me'

 insert into @t select'呅','mei'

 insert into @t select'椚','men'

 insert into @t select'掹','meng'

 insert into @t select'踎','meo'

 insert into @t select'瞇','mi'

 insert into @t select'宀','mian'

 insert into @t select'喵','miao'

 insert into @t select'乜','mie'

 insert into @t select'瓱','miliklanm'

 insert into @t select'民','min'

 insert into @t select'冧','lem'

 insert into @t select'名','ming'

 insert into @t select'谬','miu'

 insert into @t select'摸','mo'

 insert into @t select'乮','mol'

 insert into @t select'哞','mou'

 insert into @t select'母','mu'

 insert into @t select'旀','myeo'

 insert into @t select'丆','myeon'

 insert into @t select'椧','myeong'

 insert into @t select'拏','na'

 insert into @t select'腉','nai'

 insert into @t select'囡','nan'

 insert into @t select'囔','nang'

 insert into @t select'乪','keg'

 insert into @t select'孬','nao'

 insert into @t select'疒','ne'

 insert into @t select'娞','nei'

 insert into @t select'焾','nem'

 insert into @t select'嫩','nen'

 insert into @t select'莻','neus'

 insert into @t select'鈪','ngag'

 insert into @t select'銰','ngai'

 insert into @t select'啱','ngam'

 insert into @t select'妮','ni'

 insert into @t select'年','nian'

 insert into @t select'娘','niang'

 insert into @t select'茑','niao'

 insert into @t select'捏','nie'

 insert into @t select'脌','nin'

 insert into @t select'宁','ning'

 insert into @t select'牛','niu'

 insert into @t select'农','nong'

 insert into @t select'羺','nou'

 insert into @t select'奴','nu'

 insert into @t select'女','nv'

 insert into @t select'疟','nue'

 insert into @t select'瘧','nve'

 insert into @t select'奻','nuan'

 insert into @t select'黁','nun'

 insert into @t select'燶','nung'

 insert into @t select'挪','nuo'

 insert into @t select'筽','o'

 insert into @t select'夞','oes'

 insert into @t select'乯','ol'

 insert into @t select'鞰','on'

 insert into @t select'讴','ou'

 insert into @t select'妑','pa'

 insert into @t select'俳','pai'

 insert into @t select'磗','pak'

 insert into @t select'眅','pan'

 insert into @t select'乓','pang'

 insert into @t select'抛','pao'

 insert into @t select'呸','pei'

 insert into @t select'瓫','pen'

 insert into @t select'匉','peng'

 insert into @t select'浌','peol'

 insert into @t select'巼','phas'

 insert into @t select'闏','phdeng'

 insert into @t select'乶','phoi'

 insert into @t select'喸','phos'

 insert into @t select'丕','pi'

 insert into @t select'囨','pian'

 insert into @t select'缥','piao'

 insert into @t select'氕','pie'

 insert into @t select'丿','pianpang'

 insert into @t select'姘','pin'

 insert into @t select'乒','ping'

 insert into @t select'钋','po'

 insert into @t select'剖','pou'

 insert into @t select'哣','deo'

 insert into @t select'兺','ppun'

 insert into @t select'仆','pu'

 insert into @t select'七','qi'

 insert into @t select'掐','qia'

 insert into @t select'千','qian'

 insert into @t select'羌','qiang'

 insert into @t select'兛','qianke'

 insert into @t select'瓩','qianwa'

 insert into @t select'悄','qiao'

 insert into @t select'苆','qie'

 insert into @t select'亲','qin'

 insert into @t select'蠄','kem'

 insert into @t select'氢','qing'

 insert into @t select'銎','qiong'

 insert into @t select'丘','qiu'

 insert into @t select'曲','qu'

 insert into @t select'迲','keop'

 insert into @t select'峑','quan'

 insert into @t select'蒛','que'

 insert into @t select'夋','qun'

 insert into @t select'亽','ra'

 insert into @t select'囕','ram'

 insert into @t select'呥','ran'

 insert into @t select'穣','rang'

 insert into @t select'荛','rao'

 insert into @t select'惹','re'

 insert into @t select'人','ren'

 insert into @t select'扔','reng'

 insert into @t select'日','ri'

 insert into @t select'栄','rong'

 insert into @t select'禸','rou'

 insert into @t select'嶿','ru'

 insert into @t select'撋','ruan'

 insert into @t select'桵','rui'

 insert into @t select'闰','run'

 insert into @t select'叒','ruo'

 insert into @t select'仨','sa'

 insert into @t select'栍','saeng'

 insert into @t select'毢','sai'

 insert into @t select'虄','sal'

 insert into @t select'三','san'

 insert into @t select'桒','sang'

 insert into @t select'掻','sao'

 insert into @t select'色','se'

 insert into @t select'裇','sed'

 insert into @t select'聓','sei'

 insert into @t select'森','sen'

 insert into @t select'鬙','seng'

 insert into @t select'閪','seo'

 insert into @t select'縇','seon'

 insert into @t select'杀','sha'

 insert into @t select'筛','shai'

 insert into @t select'山','shan'

 insert into @t select'伤','shang'

 insert into @t select'弰','shao'

 insert into @t select'奢','she'

 insert into @t select'申','shen'

 insert into @t select'升','sheng'

 insert into @t select'尸','shi'

 insert into @t select'兙','shike'

 insert into @t select'瓧','shiwa'

 insert into @t select'収','shou'

 insert into @t select'书','shu'

 insert into @t select'刷','shua'

 insert into @t select'摔','shuai'

 insert into @t select'闩','shuan'

 insert into @t select'双','shuang'

 insert into @t select'谁','shei'

 insert into @t select'脽','shui'

 insert into @t select'吮','shun'

 insert into @t select'哾','shuo'

 insert into @t select'丝','si'

 insert into @t select'螦','so'

 insert into @t select'乺','sol'

 insert into @t select'忪','song'

 insert into @t select'凁','sou'

 insert into @t select'苏','su'

 insert into @t select'痠','suan'

 insert into @t select'夊','sui'

 insert into @t select'孙','sun'

 insert into @t select'娑','suo'

 insert into @t select'他','ta'

 insert into @t select'襨','tae'

 insert into @t select'囼','tai'

 insert into @t select'坍','tan'

 insert into @t select'铴','tang'

 insert into @t select'仐','tao'

 insert into @t select'畓','tap'

 insert into @t select'忒','te'

 insert into @t select'膯','teng'

 insert into @t select'唞','teo'

 insert into @t select'朰','teul'

 insert into @t select'剔','ti'

 insert into @t select'天','tian'

 insert into @t select'旫','tiao'

 insert into @t select'怗','tie'

 insert into @t select'厅','ting'

 insert into @t select'乭','tol'

 insert into @t select'囲','tong'

 insert into @t select'偷','tou'

 insert into @t select'凸','tu'

 insert into @t select'湍','tuan'

 insert into @t select'推','tui'

 insert into @t select'旽','tun'

 insert into @t select'乇','tuo'

 insert into @t select'屲','wa'

 insert into @t select'歪','wai'

 insert into @t select'乛','wan'

 insert into @t select'尣','wang'

 insert into @t select'危','wei'

 insert into @t select'塭','wen'

 insert into @t select'翁','weng'

 insert into @t select'挝','wo'

 insert into @t select'乌','wu'

 insert into @t select'夕','xi'

 insert into @t select'诶','ei'

 insert into @t select'疨','xia'

 insert into @t select'仙','xian'

 insert into @t select'乡','xiang'

 insert into @t select'灱','xiao'

 insert into @t select'楔','xie'

 insert into @t select'心','xin'

 insert into @t select'星','xing'

 insert into @t select'凶','xiong'

 insert into @t select'休','xiu'

 insert into @t select'旴','xu'

 insert into @t select'昍','xuan'

 insert into @t select'疶','xue'

 insert into @t select'坃','xun'

 insert into @t select'丫','ya'

 insert into @t select'咽','yan'

 insert into @t select'欕','eom'

 insert into @t select'央','yang'

 insert into @t select'吆','yao'

 insert into @t select'椰','ye'

 insert into @t select'膶','yen'

 insert into @t select'一','yi'

 insert into @t select'乁','i'

 insert into @t select'乚','yin'

 insert into @t select'应','ying'

 insert into @t select'哟','yo'

 insert into @t select'佣','yong'

 insert into @t select'优','you'

 insert into @t select'迂','yu'

 insert into @t select'囦','yuan'

 insert into @t select'曰','yue'

 insert into @t select'蒀','yun'

 insert into @t select'帀','za'

 insert into @t select'災','zai'

 insert into @t select'兂','zan'

 insert into @t select'牂','zang'

 insert into @t select'遭','zao'

 insert into @t select'啫','ze'

 insert into @t select'贼','zei'

 insert into @t select'怎','zen'

 insert into @t select'曽','zeng'

 insert into @t select'吒','zha'

 insert into @t select'甴','gad'

 insert into @t select'夈','zhai'

 insert into @t select'毡','zhan'

 insert into @t select'张','zhang'

 insert into @t select'钊','zhao'

 insert into @t select'蜇','zhe'

 insert into @t select'贞','zhen'

 insert into @t select'凧','zheng'

 insert into @t select'之','zhi'

 insert into @t select'中','zhong'

 insert into @t select'州','zhou'

 insert into @t select'劯','zhu'

 insert into @t select'抓','zhua'

 insert into @t select'专','zhuan'

 insert into @t select'转','zhuai'

 insert into @t select'妆','zhuang'

 insert into @t select'骓','zhui'

 insert into @t select'宒','zhun'

 insert into @t select'卓','zhuo'

 insert into @t select'孜','zi'

 insert into @t select'唨','zo'

 insert into @t select'宗','zong'

 insert into @t select'棸','zou'

 insert into @t select'哫','zu'

 insert into @t select'劗','zuan'

 insert into @t select'厜','zui'

 insert into @t select'尊','zun'

 insert into @t select'昨','zuo' 

 declare @strlen int

 select @strlen=len(@str),@re=''

 while @strlen>0

 begin

      select top 1 @re=UPPER(substring(py,1,1) )+substring(py,2,len(py))+@re,@strlen=@strlen-1

      from @t a where chr<=substring(@str,@strlen,1)

      order by chr collate Chinese_PRC_CS_AS_KS_WS  desc

      if @@rowcount=0

        select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1

   end

 return(@re)

end

SELECT dbo.fn_GetPying('中文字段') AS pymc

--生成拼音首码

  CREATE  function  fn_GetPy(@str  nvarchar(4000))

  returns  nvarchar(4000)

  --WITH  ENCRYPTION

  as

  begin

  declare  @intLen int

  declare  @strRet nvarchar(4000)

  declare  @temp  nvarchar(100)

  set  @intLen  =  len(@str)

  set  @strRet  =  ''

  while  @intLen  >  0

  begin

  set  @temp  =  ''

  select  @temp  =  case

  when  substring(@str,@intLen,1)  >=  '帀'  then  'Z'

  when  substring(@str,@intLen,1)  >=  '丫'  then  'Y'

  when  substring(@str,@intLen,1)  >=  '夕'  then  'X'

  when  substring(@str,@intLen,1)  >=  '屲'  then  'W'

  when  substring(@str,@intLen,1)  >=  '他'  then  'T'

  when  substring(@str,@intLen,1)  >=  '仨'  then  'S'

  when  substring(@str,@intLen,1)  >=  '呥'  then  'R'

  when  substring(@str,@intLen,1)  >=  '七'  then  'Q'

  when  substring(@str,@intLen,1)  >=  '妑'  then  'P'

  when  substring(@str,@intLen,1)  >=  '噢'  then  'O'

  when  substring(@str,@intLen,1)  >=  '拏'  then  'N'

  when  substring(@str,@intLen,1)  >=  '嘸'  then  'M'

  when  substring(@str,@intLen,1)  >=  '垃'  then  'L'

  when  substring(@str,@intLen,1)  >=  '咔'  then  'K'

  when  substring(@str,@intLen,1)  >=  '丌'  then    'J'

  when  substring(@str,@intLen,1)  >=  '铪'  then  'H'

  when  substring(@str,@intLen,1)  >=  '旮'  then  'G'

  when  substring(@str,@intLen,1)  >=  '发'  then  'F'

  when  substring(@str,@intLen,1)  >=  '妸'  then  'E'

  when  substring(@str,@intLen,1)  >=  '咑'  then  'D'

  when  substring(@str,@intLen,1)  >=  '嚓'  then  'C'

  when  substring(@str,@intLen,1)  >=  '八'  then  'B'

  when  substring(@str,@intLen,1)  >=  '吖'  then  'A'

  else  rtrim(ltrim(substring(@str,@intLen,1)))

  end

  --对于汉字特殊字符，不生成拼音码

  if  (ascii(@temp)>127)  set  @temp  =  ''

  --对于英文中小括号，不生成拼音码

  if  @temp  =  '('  or  @temp  =  ')'  set  @temp  =  ''

  select  @strRet  =  @temp  +  @strRet

  set  @intLen  =  @intLen  -  1

  end

  return  lower(@strRet)

  end  

SELECT dbo.fn_GetPy('中文字段') AS pymc