| 订阅 | 在线投稿
分享
 
 
当前位置: 王朝网络 >> mssql >> DB2 zhong chuang jian yi ge huo qu han zi pin yin shou zi mu de SQL han shu
 

DB2 zhong chuang jian yi ge huo qu han zi pin yin shou zi mu de SQL han shu

2008-07-17 07:48:18 编辑來源:互联网 评论
 
本文为【DB2中创建一个获取汉字拼音首字母的SQL函数】的拼音翻译版
  xu qiu
  you xie shi hou wo men hui you zhe yang de xu qiu , yao qiu shi yong zi mu cong a zhi z dui yi zu shu ju jin xing suo yin , ru guo shu ju de ge shi quan bu shi ban jiao de ying wen ze hen rong yi shi xian , dan ruo shi dui yi zu zhong wen shu ju jin xing suo yin ze hui yin qi yi dian xiao de ma fan , shu ju zai lu ru shu ju ku de shi hou ke neng bing mei you zhi ding yi ge suo yin zi mu , zhe jiu yao qiu ying yong cheng xu ke yi zi dong sheng cheng yong yu suo yin de xin xi 。
  yi ban dui yu zhong wen shu ju de suo yin , cai yong ci zu de shou han zi pin yin de shou zi mu , li ru :
  ci zu suo yin zi mu
  --- -----
  xiong mao x
  bai ji tun b
  cang ye lv z
  zai DB2 zhong bing mei you ti gong xiang ying de han shu ke yi qu de han zi pin yin de shou zi mu , wo men ke yi li yong shu ju ku zhen dui zhong wen zi fu ji de pai xu gong neng chuang jian yi ge zhe yang de han shu 。
  gong zuo yuan li
  wo men zhi dao zai shi yong zhong wen zi fu ji de shu ju ku zhong , dang ni dui yi lie zhong wen shu ju shi yong order by pai xu shi , pai xu de jie guo zheng shi an zhao mei xing ji lu di yi ge han zi de pin yin shou zi mu jin xing pai lie de , na me wo men xu yao xiang ban fa qu de zhe ge zi mu 。
  dan shi shu ju ku nei bu shi ru he zuo dao zhe yi dian de ne ? yi zhong wen zi fu ji GBK wei li , rang wo men cha kan yi xia GBK zi fu ji de nei ma biao , wo men jin zhai chu yi duan :
  0 1 2 3 4 5 6 7 8 9 A B C D E F
  B040 liu lao xian fei dan yin he xian guan gui nong yu wei yong lei li
  B050 shu dan bing bing bie ji chi yang jie zheng me li huo la ji xuan
  B060 ying yin yong tan dian luo luan luan bo uu ba fa bo qie bi zao
  B070 mao ba ci ling mo ji peng gao e han bi chou qian ai po
  B080 hao huang hao ze cui hao xiao ye hao jiao ai xing huang bo piao he
  B090 jiao gan pao qiu que zha gu jun jun zhou cu uu dan du qi ying
  B0A0 bei a a ai ai ai ai ai ai ai ai ai ai ai ai ai
  B0B0 an an an an an an an an an ang ang ang ao ao ao ao
  B0C0 ao ao ao ao ao ba ba ba ba ba ba ba ba ba ba ba
  B0D0 ba ba ba ba ba ba ba bai bai bai bai bai bai bai bai ban
  B0E0 ban ban ban ban ban ban ban ban ban ban ban ban ban ban bang bang
  B0F0 bang bang pang bang bang bang bang bang bang bang bao bao bao bao bo
  ke yi kan dao cong B0A0-1 kai shi , zhi B0C0-5, shi pin yin A kai tou de han zi , qia hao shi an zhao pin yin zi mu de xian hou shun xu pai lie , bing qie ba yin tiao de yin su ye kao lv jin qu le , you ci , ke yi tui duan chu , shu ju ku zai GBk bian ma de shu ju ku zhong dui han zi jin xing pai xu , ji shi yi zhao zi fu nei ma biao de bian ma jin xing de 。
  wo men ba B0C0-5 wei zhi de han zi ji lu xia lai , ji “ ao ” zi , zhe shi yi “a” pin yin kai tou zai nei ma biao zhong pai lie zai zui hou de han zi , yong tong yang de fang fa , wo men zhao chu suo you yi pin yin cong b zhi z kai tou , zai nei ma biao zhong pai lie zai zui hou de han zi , yu 26 ge zi mu de dui ying guan xi ru xia :
  ' ao ' a
  ' bu ' b
  ' cuo ' c
  ' duo ' d
  ' er ' e
  ' fu ' f
  ' guo ' g
  ' huo ' h
  i
  ' jun ' j
  ' kuo ' k
  ' luo ' l
  ' mu ' m
  ' nuo ' n
  ' ou ' o
  ' pu ' p
  ' qun ' q
  ' ruo ' r
  ' suo ' s
  ' tuo ' t
  u
  v
  ' wu ' w
  ' xun ' x
  ' yun ' y
  ' zuo z
  zhu : mei you yi 'i','u','v' kai tou de han yu pin yin 。
  xian zai jia ruo wo men na chu ren he yi ge han zi , fang zai wo men tiao xuan chu de zhe xie han zi zhong jian , li yong shu ju ku jin xing yi ci shi yong GBK zi fu ji de pai xu , wo men bian neng gou gen ju zhe ge han zi pai lie de xiang dui wei zhi de dao qi pin yin shou zi mu 。
  li yong sql yu ju sheng cheng yi zu shang shu han zi de jie guo ji , wo men jiang 'i','u','v' san ge kong que han zi de wei zhi bu shang le shang yi ge pin yin de han zi ,
  select t1.strChn
  from ( select ' ao ' strChn from sysibm.sysdummy1
  union all
  select ' bu ' strChn from sysibm.sysdummy1
  union all
  select ' cuo ' strChn from sysibm.sysdummy1
  union all
  select ' duo ' strChn from sysibm.sysdummy1
  union all
  select ' er ' strChn from sysibm.sysdummy1
  union all
  select ' fu ' strChn from sysibm.sysdummy1
  union all
  select ' guo ' strChn from sysibm.sysdummy1
  union all
  select ' huo ' strChn from sysibm.sysdummy1
  union all
  select ' huo ' strChn from sysibm.sysdummy1
  union all
  select ' jun ' strChn from sysibm.sysdummy1
  union all
  select ' kuo ' strChn from sysibm.sysdummy1
  union all
  select ' luo ' strChn from sysibm.sysdummy1
  union all
  select ' mu ' strChn from sysibm.sysdummy1
  union all
  select ' nuo ' strChn from sysibm.sysdummy1
  union all
  select ' ou ' strChn from sysibm.sysdummy1
  union all
  select ' pu ' strChn from sysibm.sysdummy1
  union all
  select ' qun ' strChn from sysibm.sysdummy1
  union all
  select ' ruo ' strChn from sysibm.sysdummy1
  union all
  select ' suo ' strChn from sysibm.sysdummy1
  union all
  select ' tuo ' strChn from sysibm.sysdummy1
  union all
  select ' tuo ' strChn from sysibm.sysdummy1
  union all
  select ' tuo ' strChn from sysibm.sysdummy1
  union all
  select ' wu ' strChn from sysibm.sysdummy1
  union all
  select ' xun ' strChn from sysibm.sysdummy1
  union all
  select ' yun ' strChn from sysibm.sysdummy1
  union all
  select ' zuo ' strChn from sysibm.sysdummy1
  ) as t1
  shi xian
  jie xia lai hen fang bian de jiu ke yi xie chu zhe ge han shu de ju ti shi xian , zai shi xian de dai ma zhong , wo men you jia ru le zhen dui ying wen zi mu de chu li , han shu bian yi hou , ke tong guo ru xia fang shi tiao yong :
  select getIndex( ' suo ' ) index from dual;
  index
  ------
  f
  yuan dai ma ru xia :
  create function getIndex (
  in_strChn varchar(2)
  ) returns char(1)
  language sql
  external action
  reads sql data
  begin atomic
  declare chResult char(1);
  declare n integer default 0;
  if( in_strChn = '' or in_strChn is null or lengthb( in_strChn ) > 2 ) then
  return null;
  end if;
  if(( ascii( in_strChn ) >= ascii('A') and ascii( in_strChn ) <= ascii('Z') )
  or ( ascii( in_strChn ) >= ascii('a') and ascii( in_strChn ) <= ascii('z')) ) then
  return lcase( substr( in_strChn, 1, 1 ) );
  end if;
  for myloop as
  select t2.strChn
  from ( select t1.strChn
  from ( select ' ao ' strChn from sysibm.sysdummy1
  union all
  select ' bu ' strChn from sysibm.sysdummy1
  union all
  select ' cuo ' strChn from sysibm.sysdummy1
  union all
  select ' duo ' strChn from sysibm.sysdummy1
  union all
  select ' er ' strChn from sysibm.sysdummy1
  union all
  select ' fu ' strChn from sysibm.sysdummy1
  union all
  select ' guo ' strChn from sysibm.sysdummy1
  union all
  select ' huo ' strChn from sysibm.sysdummy1
  union all
  select ' huo ' strChn from sysibm.sysdummy1
  union all
  select ' jun ' strChn from sysibm.sysdummy1
  union all
  select ' kuo ' strChn from sysibm.sysdummy1
  union all
  select ' luo ' strChn from sysibm.sysdummy1
  union all
  select ' mu ' strChn from sysibm.sysdummy1
  union all
  select ' nuo ' strChn from sysibm.sysdummy1
  union all
  select ' ou ' strChn from sysibm.sysdummy1
  union all
  select ' pu ' strChn from sysibm.sysdummy1
  union all
  select ' qun ' strChn from sysibm.sysdummy1
  union all
  select ' ruo ' strChn from sysibm.sysdummy1
  union all
  select ' suo ' strChn from sysibm.sysdummy1
  union all
  select ' tuo ' strChn from sysibm.sysdummy1
  union all
  select ' tuo ' strChn from sysibm.sysdummy1
  union all
  select ' tuo ' strChn from sysibm.sysdummy1
  union all
  select ' wu ' strChn from sysibm.sysdummy1
  union all
  select ' xun ' strChn from sysibm.sysdummy1
  union all
  select ' yun ' strChn from sysibm.sysdummy1
  union all
  select ' zuo ' strChn from sysibm.sysdummy1
  union all
  select in_strChn strChn from sysibm.sysdummy1
  ) as t1
  order by t1.strChn
  ) as t2
  do
  if ( strChn = in_strChn ) then
  set chResult = chr( ascii('a') + ( case n when 26 then n-1 else n end ) );
  return chResult;
  end if;
  set n = n + 1;
  end for;
  return chResult;
  end@
  shi ji shi yong zhong , ying zhu yi jian li shu ju ku shi zi fu ji shen shu de she zhi , ying shi yong GBK zi fu ji 。
  ying yong yi xia ming ling cha kan yi jian li shu ju ku de zi fu ji :
  db2 connect to db_name user user_name using password
  db2 get db cfg | grep -i 'code set'
  ci shen shu zai shu ju ku jian li zhi hou bu neng xiu gai 。【原文】【汉音对照
 
 
 
 
 
 
 
 
日版宠物情人插曲《Winding Road》歌词

日版宠物情人2017的插曲,很带节奏感,日语的,女生唱的。 最后听见是在第8集的时候女主手割伤了,然后男主用嘴帮她吸了一下,插曲就出来了。 歌手:Def...

兄弟共妻,我成了他们夜里的美食

老钟家的两个儿子很特别,就是跟其他的人不太一样,魔一般的执着。兄弟俩都到了要结婚的年龄了,不管自家老爹怎么磨破嘴皮子,兄弟俩说不娶就不娶,老父母为兄弟两操碎了心...

如何磨出破洞牛仔裤?牛仔裤怎么剪破洞?

把牛仔裤磨出有线的破洞 1、具体工具就是磨脚石,下面垫一个硬物,然后用磨脚石一直磨一直磨,到把那块磨薄了,用手撕开就好了。出来的洞啊很自然的。需要猫须的话调几...

我就是扫描下图得到了敬业福和爱国福

先来看下敬业福和爱国福 今年春节,支付宝再次推出了“五福红包”活动,表示要“把欠大家的敬业福都还给大家”。 今天该活动正式启动,和去年一样,需要收集“五福”...

冰箱异味产生的原因和臭味去除的方法

有时候我们打开冰箱就会闻到一股异味,冰箱里的这种异味是因为一些物质发出的气味的混合体,闻起来让人恶心。 产生这些异味的主要原因有以下几点。 1、很多人有这种习...

 
 
xu qiu you xie shi hou wo men hui you zhe yang de xu qiu , yao qiu shi yong zi mu cong a zhi z dui yi zu shu ju jin xing suo yin , ru guo shu ju de ge shi quan bu shi ban jiao de ying wen ze hen rong yi shi xian , dan ruo shi dui yi zu zhong wen shu ju jin xing suo yin ze hui yin qi yi dian xiao de ma fan , shu ju zai lu ru shu ju ku de shi hou ke neng bing mei you zhi ding yi ge suo yin zi mu , zhe jiu yao qiu ying yong cheng xu ke yi zi dong sheng cheng yong yu suo yin de xin xi 。 yi ban dui yu zhong wen shu ju de suo yin , cai yong ci zu de shou han zi pin yin de shou zi mu , li ru : ci zu suo yin zi mu --- ----- xiong mao x bai ji tun b cang ye lv z zai DB2 zhong bing mei you ti gong xiang ying de han shu ke yi qu de han zi pin yin de shou zi mu , wo men ke yi li yong shu ju ku zhen dui zhong wen zi fu ji de pai xu gong neng chuang jian yi ge zhe yang de han shu 。 gong zuo yuan li wo men zhi dao zai shi yong zhong wen zi fu ji de shu ju ku zhong , dang ni dui yi lie zhong wen shu ju shi yong order by pai xu shi , pai xu de jie guo zheng shi an zhao mei xing ji lu di yi ge han zi de pin yin shou zi mu jin xing pai lie de , na me wo men xu yao xiang ban fa qu de zhe ge zi mu 。 dan shi shu ju ku nei bu shi ru he zuo dao zhe yi dian de ne ? yi zhong wen zi fu ji GBK wei li , rang wo men cha kan yi xia GBK zi fu ji de nei ma biao , wo men jin zhai chu yi duan : 0 1 2 3 4 5 6 7 8 9 A B C D E F B040 liu lao xian fei dan yin he xian guan gui nong yu wei yong lei li B050 shu dan bing bing bie ji chi yang jie zheng me li huo la ji xuan B060 ying yin yong tan dian luo luan luan bo uu ba fa bo qie bi zao B070 mao ba ci ling mo ji peng gao e han bi chou qian ai po B080 hao huang hao ze cui hao xiao ye hao jiao ai xing huang bo piao he B090 jiao gan pao qiu que zha gu jun jun zhou cu uu dan du qi ying B0A0 bei a a ai ai ai ai ai ai ai ai ai ai ai ai ai B0B0 an an an an an an an an an ang ang ang ao ao ao ao B0C0 ao ao ao ao ao ba ba ba ba ba ba ba ba ba ba ba B0D0 ba ba ba ba ba ba ba bai bai bai bai bai bai bai bai ban B0E0 ban ban ban ban ban ban ban ban ban ban ban ban ban ban bang bang B0F0 bang bang pang bang bang bang bang bang bang bang bao bao bao bao bo ke yi kan dao cong B0A0-1 kai shi , zhi B0C0-5, shi pin yin A kai tou de han zi , qia hao shi an zhao pin yin zi mu de xian hou shun xu pai lie , bing qie ba yin tiao de yin su ye kao lv jin qu le , you ci , ke yi tui duan chu , shu ju ku zai GBk bian ma de shu ju ku zhong dui han zi jin xing pai xu , ji shi yi zhao zi fu nei ma biao de bian ma jin xing de 。 wo men ba B0C0-5 wei zhi de han zi ji lu xia lai , ji “ ao ” zi , zhe shi yi “a” pin yin kai tou zai nei ma biao zhong pai lie zai zui hou de han zi , yong tong yang de fang fa , wo men zhao chu suo you yi pin yin cong b zhi z kai tou , zai nei ma biao zhong pai lie zai zui hou de han zi , yu 26 ge zi mu de dui ying guan xi ru xia : ' ao ' a ' bu ' b ' cuo ' c ' duo ' d ' er ' e ' fu ' f ' guo ' g ' huo ' h i ' jun ' j ' kuo ' k ' luo ' l ' mu ' m ' nuo ' n ' ou ' o ' pu ' p ' qun ' q ' ruo ' r ' suo ' s ' tuo ' t u v ' wu ' w ' xun ' x ' yun ' y ' zuo z zhu : mei you yi 'i','u','v' kai tou de han yu pin yin 。 xian zai jia ruo wo men na chu ren he yi ge han zi , fang zai wo men tiao xuan chu de zhe xie han zi zhong jian , li yong shu ju ku jin xing yi ci shi yong GBK zi fu ji de pai xu , wo men bian neng gou gen ju zhe ge han zi pai lie de xiang dui wei zhi de dao qi pin yin shou zi mu 。 li yong sql yu ju sheng cheng yi zu shang shu han zi de jie guo ji , wo men jiang 'i','u','v' san ge kong que han zi de wei zhi bu shang le shang yi ge pin yin de han zi , select t1.strChn from ( select ' ao ' strChn from sysibm.sysdummy1 union all select ' bu ' strChn from sysibm.sysdummy1 union all select ' cuo ' strChn from sysibm.sysdummy1 union all select ' duo ' strChn from sysibm.sysdummy1 union all select ' er ' strChn from sysibm.sysdummy1 union all select ' fu ' strChn from sysibm.sysdummy1 union all select ' guo ' strChn from sysibm.sysdummy1 union all select ' huo ' strChn from sysibm.sysdummy1 union all select ' huo ' strChn from sysibm.sysdummy1 union all select ' jun ' strChn from sysibm.sysdummy1 union all select ' kuo ' strChn from sysibm.sysdummy1 union all select ' luo ' strChn from sysibm.sysdummy1 union all select ' mu ' strChn from sysibm.sysdummy1 union all select ' nuo ' strChn from sysibm.sysdummy1 union all select ' ou ' strChn from sysibm.sysdummy1 union all select ' pu ' strChn from sysibm.sysdummy1 union all select ' qun ' strChn from sysibm.sysdummy1 union all select ' ruo ' strChn from sysibm.sysdummy1 union all select ' suo ' strChn from sysibm.sysdummy1 union all select ' tuo ' strChn from sysibm.sysdummy1 union all select ' tuo ' strChn from sysibm.sysdummy1 union all select ' tuo ' strChn from sysibm.sysdummy1 union all select ' wu ' strChn from sysibm.sysdummy1 union all select ' xun ' strChn from sysibm.sysdummy1 union all select ' yun ' strChn from sysibm.sysdummy1 union all select ' zuo ' strChn from sysibm.sysdummy1 ) as t1 shi xian jie xia lai hen fang bian de jiu ke yi xie chu zhe ge han shu de ju ti shi xian , zai shi xian de dai ma zhong , wo men you jia ru le zhen dui ying wen zi mu de chu li , han shu bian yi hou , ke tong guo ru xia fang shi tiao yong : select getIndex( ' suo ' ) index from dual; index ------ f yuan dai ma ru xia : create function getIndex ( in_strChn varchar(2) ) returns char(1) language sql external action reads sql data begin atomic declare chResult char(1); declare n integer default 0; if( in_strChn = '' or in_strChn is null or lengthb( in_strChn ) > 2 ) then return null; end if; if(( ascii( in_strChn ) >= ascii('A') and ascii( in_strChn ) <= ascii('Z') ) or ( ascii( in_strChn ) >= ascii('a') and ascii( in_strChn ) <= ascii('z')) ) then return lcase( substr( in_strChn, 1, 1 ) ); end if; for myloop as select t2.strChn from ( select t1.strChn from ( select ' ao ' strChn from sysibm.sysdummy1 union all select ' bu ' strChn from sysibm.sysdummy1 union all select ' cuo ' strChn from sysibm.sysdummy1 union all select ' duo ' strChn from sysibm.sysdummy1 union all select ' er ' strChn from sysibm.sysdummy1 union all select ' fu ' strChn from sysibm.sysdummy1 union all select ' guo ' strChn from sysibm.sysdummy1 union all select ' huo ' strChn from sysibm.sysdummy1 union all select ' huo ' strChn from sysibm.sysdummy1 union all select ' jun ' strChn from sysibm.sysdummy1 union all select ' kuo ' strChn from sysibm.sysdummy1 union all select ' luo ' strChn from sysibm.sysdummy1 union all select ' mu ' strChn from sysibm.sysdummy1 union all select ' nuo ' strChn from sysibm.sysdummy1 union all select ' ou ' strChn from sysibm.sysdummy1 union all select ' pu ' strChn from sysibm.sysdummy1 union all select ' qun ' strChn from sysibm.sysdummy1 union all select ' ruo ' strChn from sysibm.sysdummy1 union all select ' suo ' strChn from sysibm.sysdummy1 union all select ' tuo ' strChn from sysibm.sysdummy1 union all select ' tuo ' strChn from sysibm.sysdummy1 union all select ' tuo ' strChn from sysibm.sysdummy1 union all select ' wu ' strChn from sysibm.sysdummy1 union all select ' xun ' strChn from sysibm.sysdummy1 union all select ' yun ' strChn from sysibm.sysdummy1 union all select ' zuo ' strChn from sysibm.sysdummy1 union all select in_strChn strChn from sysibm.sysdummy1 ) as t1 order by t1.strChn ) as t2 do if ( strChn = in_strChn ) then set chResult = chr( ascii('a') + ( case n when 26 then n-1 else n end ) ); return chResult; end if; set n = n + 1; end for; return chResult; end@ shi ji shi yong zhong , ying zhu yi jian li shu ju ku shi zi fu ji shen shu de she zhi , ying shi yong GBK zi fu ji 。 ying yong yi xia ming ling cha kan yi jian li shu ju ku de zi fu ji : db2 connect to db_name user user_name using password db2 get db cfg | grep -i 'code set' ci shen shu zai shu ju ku jian li zhi hou bu neng xiu gai 。
󰈣󰈤
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
越来越漂亮的楚楚(7)
越来越漂亮的楚楚(6)
越来越漂亮的楚楚(5)
越来越漂亮的楚楚(4)
共长一色
水中莲
峰林晚照
远离喧嚣——山村小景
 
>>返回首页<<
 为你推荐
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 热帖排行
 
 
 
 
©2005- 王朝网络 版权所有