SQL Server下汉字转拼音语句

合集下载

汉字转拼音的代码

汉字转拼音的代码Function pinyin(p As String) As Stringi = Asc(p)Select Case iCase -20319 To -20318: pinyin = "a " Case -20317 To -20305: pinyin = "ai " Case -20304 To -20296: pinyin = "an " Case -20295 To -20293: pinyin = "ang " Case -20292 To -20284: pinyin = "ao " Case -20283 To -20266: pinyin = "ba " Case -20265 To -20258: pinyin = "bai " Case -20257 To -20243: pinyin = "ban " Case -20242 To -20231: pinyin = "bang " Case -20230 To -20052: pinyin = "bao " Case -20051 To -20037: pinyin = "bei "Case -20036 To -20033: pinyin = "ben " Case -20032 To -20027: pinyin = "beng " Case -20026 To -20003: pinyin = "bi " Case -20002 To -19991: pinyin = "bian " Case -19990 To -19987: pinyin = "biao " Case -19986 To -19983: pinyin = "bie " Case -19982 To -19977: pinyin = "bin " Case -19976 To -19806: pinyin = "bing " Case -19805 To -19785: pinyin = "bo " Case -19784 To -19776: pinyin = "bu " Case -19775 To -19775: pinyin = "ca " Case -19774 To -19764: pinyin = "cai " Case -19763 To -19757: pinyin = "can " Case -19756 To -19752: pinyin = "cang " Case -19751 To -19747: pinyin = "cao "Case -19746 To -19742: pinyin = "ce " Case -19741 To -19740: pinyin = "ceng " Case -19739 To -19729: pinyin = "cha " Case -19728 To -19726: pinyin = "chai " Case -19725 To -19716: pinyin = "chan " Case -19715 To -19541: pinyin = "chang " Case -19540 To -19532: pinyin = "chao " Case -19531 To -19526: pinyin = "che " Case -19525 To -19516: pinyin = "chen " Case -19515 To -19501: pinyin = "cheng " Case -19500 To -19485: pinyin = "chi " Case -19484 To -19480: pinyin = "chong " Case -19479 To -19468: pinyin = "chou " Case -19467 To -19290: pinyin = "chu " Case -19289 To -19289: pinyin = "chuai "Case -19281 To -19276: pinyin = "chuang " Case -19275 To -19271: pinyin = "chui " Case -19270 To -19264: pinyin = "chun " Case -19263 To -19262: pinyin = "chuo " Case -19261 To -19250: pinyin = "ci " Case -19249 To -19244: pinyin = "cong " Case -19243 To -19243: pinyin = "cou " Case -19242 To -19239: pinyin = "cu " Case -19238 To -19236: pinyin = "cuan " Case -19235 To -19228: pinyin = "cui " Case -19227 To -19225: pinyin = "cun " Case -19224 To -19219: pinyin = "cuo " Case -19218 To -19213: pinyin = "da " Case -19212 To -19039: pinyin = "dai "Case -19023 To -19019: pinyin = "dang " Case -19018 To -19007: pinyin = "dao " Case -19006 To -19004: pinyin = "de " Case -19003 To -18997: pinyin = "deng " Case -18996 To -18978: pinyin = "di " Case -18977 To -18962: pinyin = "dian " Case -18961 To -18953: pinyin = "diao " Case -18952 To -18784: pinyin = "die " Case -18783 To -18775: pinyin = "ding " Case -18774 To -18774: pinyin = "diu " Case -18773 To -18527: pinyin = "dong " Case -18526 To -18519: pinyin = "fa " Case -18518 To -18502: pinyin = "fan " Case -18501 To -18491: pinyin = "fang "Case -18490 To -18479: pinyin = "fei " Case -18478 To -18464: pinyin = "fen " Case -18463 To -18449: pinyin = "feng " Case -18448 To -18448: pinyin = "fo " Case -18447 To -18447: pinyin = "fou " Case -18446 To -18240: pinyin = "fu " Case -18239 To -18238: pinyin = "ga " Case -18237 To -18232: pinyin = "gai " Case -18231 To -18221: pinyin = "gan " Case -18220 To -18212: pinyin = "gang " Case -18211 To -18202: pinyin = "gao " Case -18201 To -18185: pinyin = "ge " Case -18184 To -18184: pinyin = "gei " Case -18183 To -18182: pinyin = "gen " Case -18181 To -18013: pinyin = "geng "Case -18012 To -17998: pinyin = "gong " Case -17997 To -17989: pinyin = "gou " Case -17988 To -17971: pinyin = "gu " Case -17970 To -17965: pinyin = "gua " Case -17964 To -17962: pinyin = "guai " Case -17961 To -17951: pinyin = "guan " Case -17950 To -17948: pinyin = "guang " Case -17947 To -17932: pinyin = "gui " Case -17931 To -17929: pinyin = "gun " Case -17928 To -17923: pinyin = "guo " Case -17922 To -17760: pinyin = "ha " Case -17759 To -17753: pinyin = "hai " Case -17752 To -17734: pinyin = "han " Case -17733 To -17731: pinyin = "hang " Case -17730 To -17722: pinyin = "hao "Case -17703 To -17702: pinyin = "hei " Case -17701 To -17698: pinyin = "hen " Case -17697 To -17693: pinyin = "heng " Case -17692 To -17684: pinyin = "hong " Case -17683 To -17677: pinyin = "hou " Case -17676 To -17497: pinyin = "hu " Case -17496 To -17488: pinyin = "hua " Case -17487 To -17483: pinyin = "huai " Case -17482 To -17469: pinyin = "huan " Case -17468 To -17455: pinyin = "huang " Case -17454 To -17434: pinyin = "hui " Case -17433 To -17428: pinyin = "hun " Case -17427 To -17418: pinyin = "huo " Case -17417 To -17203: pinyin = "ji "Case -17185 To -16984: pinyin = "jian "Case -16983 To -16971: pinyin = "jiang " Case -16970 To -16943: pinyin = "jiao " Case -16942 To -16916: pinyin = "jie " Case -16915 To -16734: pinyin = "jin " Case -16733 To -16709: pinyin = "jing " Case -16708 To -16707: pinyin = "jiong " Case -16706 To -16690: pinyin = "jiu " Case -16689 To -16665: pinyin = "ju " Case -16664 To -16658: pinyin = "juan "Case -16657 To -16648: pinyin = "jue "Case -16647 To -16475: pinyin = "jun " Case -16474 To -16471: pinyin = "ka " Case -16470 To -16466: pinyin = "kai "Case -16465 To -16460: pinyin = "kan " Case -16459 To -16453: pinyin = "kang " Case -16452 To -16449: pinyin = "kao " Case -16448 To -16434: pinyin = "ke " Case -16433 To -16430: pinyin = "ken " Case -16429 To -16428: pinyin = "keng " Case -16427 To -16424: pinyin = "kong " Case -16423 To -16420: pinyin = "kou " Case -16419 To -16413: pinyin = "ku " Case -16412 To -16408: pinyin = "kua " Case -16407 To -16404: pinyin = "kuai " Case -16403 To -16402: pinyin = "kuan " Case -16401 To -16394: pinyin = "kuang " Case -16393 To -16221: pinyin = "kui " Case -16220 To -16217: pinyin = "kun "Case -16216 To -16213: pinyin = "kuo " Case -16212 To -16206: pinyin = "la " Case -16205 To -16203: pinyin = "lai " Case -16202 To -16188: pinyin = "lan " Case -16187 To -16181: pinyin = "lang " Case -16180 To -16172: pinyin = "lao " Case -16171 To -16170: pinyin = "le " Case -16169 To -16159: pinyin = "lei " Case -16158 To -16156: pinyin = "leng " Case -16155 To -15960: pinyin = "li " Case -15959 To -15959: pinyin = "lia " Case -15958 To -15945: pinyin = "lian " Case -15944 To -15934: pinyin = "liang " Case -15933 To -15921: pinyin = "liao " Case -15920 To -15916: pinyin = "lie "Case -15915 To -15904: pinyin = "lin " Case -15903 To -15890: pinyin = "ling " Case -15889 To -15879: pinyin = "liu " Case -15878 To -15708: pinyin = "long " Case -15707 To -15702: pinyin = "lou " Case -15701 To -15682: pinyin = "lu " Case -15681 To -15668: pinyin = "lv " Case -15667 To -15662: pinyin = "luan " Case -15661 To -15660: pinyin = "lue " Case -15659 To -15653: pinyin = "lun " Case -15652 To -15641: pinyin = "luo " Case -15640 To -15632: pinyin = "ma " Case -15631 To -15626: pinyin = "mai " Case -15625 To -15455: pinyin = "man " Case -15454 To -15449: pinyin = "mang "Case -15448 To -15437: pinyin = "mao " Case -15436 To -15436: pinyin = "me " Case -15435 To -15420: pinyin = "mei " Case -15419 To -15417: pinyin = "men " Case -15416 To -15409: pinyin = "meng " Case -15408 To -15395: pinyin = "mi " Case -15394 To -15386: pinyin = "mian " Case -15385 To -15378: pinyin = "miao " Case -15377 To -15376: pinyin = "mie " Case -15375 To -15370: pinyin = "min " Case -15369 To -15364: pinyin = "ming " Case -15363 To -15363: pinyin = "miu " Case -15362 To -15184: pinyin = "mo " Case -15183 To -15181: pinyin = "mou " Case -15180 To -15166: pinyin = "mu "Case -15165 To -15159: pinyin = "na " Case -15158 To -15154: pinyin = "nai " Case -15153 To -15151: pinyin = "nan " Case -15150 To -15150: pinyin = "nang " Case -15149 To -15145: pinyin = "nao " Case -15144 To -15144: pinyin = "ne " Case -15143 To -15142: pinyin = "nei " Case -15141 To -15141: pinyin = "nen " Case -15140 To -15140: pinyin = "neng " Case -15139 To -15129: pinyin = "ni " Case -15128 To -15122: pinyin = "nian " Case -15121 To -15120: pinyin = "niang " Case -15119 To -15118: pinyin = "niao " Case -15117 To -15111: pinyin = "nie "Case -15110 To -15110: pinyin = "nin " Case -15109 To -14942: pinyin = "ning " Case -14941 To -14938: pinyin = "niu " Case -14937 To -14934: pinyin = "nong " Case -14933 To -14931: pinyin = "nu " Case -14930 To -14930: pinyin = "nv " Case -14929 To -14929: pinyin = "nuan " Case -14928 To -14927: pinyin = "nue " Case -14926 To -14923: pinyin = "nuo " Case -14922 To -14922: pinyin = "o " Case -14921 To -14915: pinyin = "ou " Case -14914 To -14909: pinyin = "pa " Case -14908 To -14903: pinyin = "pai " Case -14902 To -14895: pinyin = "pan " Case -14894 To -14890: pinyin = "pang "Case -14889 To -14883: pinyin = "pao " Case -14882 To -14874: pinyin = "pei " Case -14873 To -14872: pinyin = "pen " Case -14871 To -14858: pinyin = "peng " Case -14857 To -14679: pinyin = "pi " Case -14678 To -14675: pinyin = "pian " Case -14674 To -14671: pinyin = "piao " Case -14670 To -14669: pinyin = "pie " Case -14668 To -14664: pinyin = "pin " Case -14663 To -14655: pinyin = "ping " Case -14654 To -14646: pinyin = "po " Case -14645 To -14631: pinyin = "pu " Case -14630 To -14595: pinyin = "qi " Case -14594 To -14430: pinyin = "qia " Case -14429 To -14408: pinyin = "qian "Case -14407 To -14400: pinyin = "qiang " Case -14399 To -14385: pinyin = "qiao " Case -14384 To -14380: pinyin = "qie " Case -14379 To -14369: pinyin = "qin " Case -14368 To -14356: pinyin = "qing " Case -14355 To -14354: pinyin = "qiong " Case -14353 To -14346: pinyin = "qiu " Case -14345 To -14171: pinyin = "qu " Case -14170 To -14160: pinyin = "quan " Case -14159 To -14152: pinyin = "que " Case -14151 To -14150: pinyin = "qun " Case -14149 To -14146: pinyin = "ran " Case -14145 To -14141: pinyin = "rang " Case -14140 To -14138: pinyin = "rao " Case -14137 To -14136: pinyin = "re "Case -14135 To -14126: pinyin = "ren " Case -14125 To -14124: pinyin = "reng " Case -14123 To -14123: pinyin = "ri " Case -14122 To -14113: pinyin = "rong " Case -14112 To -14110: pinyin = "rou " Case -14109 To -14100: pinyin = "ru " Case -14099 To -14098: pinyin = "ruan " Case -14097 To -14095: pinyin = "rui " Case -14094 To -14093: pinyin = "run " Case -14092 To -14091: pinyin = "ruo " Case -14090 To -14088: pinyin = "sa " Case -14087 To -14084: pinyin = "sai " Case -14083 To -13918: pinyin = "san " Case -13917 To -13915: pinyin = "sang " Case -13914 To -13911: pinyin = "sao "Case -13910 To -13908: pinyin = "se " Case -13907 To -13907: pinyin = "sen " Case -13906 To -13906: pinyin = "seng " Case -13905 To -13897: pinyin = "sha " Case -13896 To -13895: pinyin = "shai " Case -13894 To -13879: pinyin = "shan " Case -13878 To -13871: pinyin = "shang " Case -13870 To -13860: pinyin = "shao " Case -13859 To -13848: pinyin = "she " Case -13847 To -13832: pinyin = "shen " Case -13831 To -13659: pinyin = "sheng " Case -13658 To -13612: pinyin = "shi " Case -13611 To -13602: pinyin = "shou " Case -13601 To -13407: pinyin = "shu " Case -13406 To -13405: pinyin = "shua "Case -13404 To -13401: pinyin = "shuai " Case -13400 To -13399: pinyin = "shuan " Case -13398 To -13396: pinyin = "shuang " Case -13395 To -13392: pinyin = "shui " Case -13391 To -13388: pinyin = "shun " Case -13387 To -13384: pinyin = "shuo " Case -13383 To -13368: pinyin = "si " Case -13367 To -13360: pinyin = "song " Case -13359 To -13357: pinyin = "sou " Case -13356 To -13344: pinyin = "su " Case -13343 To -13341: pinyin = "suan " Case -13340 To -13330: pinyin = "sui " Case -13329 To -13327: pinyin = "sun " Case -13326 To -13319: pinyin = "suo " Case -13318 To -13148: pinyin = "ta "Case -13147 To -13139: pinyin = "tai " Case -13138 To -13121: pinyin = "tan " Case -13120 To -13108: pinyin = "tang " Case -13107 To -13097: pinyin = "tao " Case -13096 To -13096: pinyin = "te " Case -13095 To -13092: pinyin = "teng " Case -13091 To -13077: pinyin = "ti " Case -13076 To -13069: pinyin = "tian " Case -13068 To -13064: pinyin = "tiao " Case -13063 To -13061: pinyin = "tie " Case -13060 To -12889: pinyin = "ting " Case -12888 To -12876: pinyin = "tong " Case -12875 To -12872: pinyin = "tou " Case -12871 To -12861: pinyin = "tu " Case -12860 To -12859: pinyin = "tuan "Case -12858 To -12853: pinyin = "tui "Case -12852 To -12850: pinyin = "tun " Case -12849 To -12839: pinyin = "tuo " Case -12838 To -12832: pinyin = "wa " Case -12831 To -12830: pinyin = "wai " Case -12829 To -12813: pinyin = "wan " Case -12812 To -12803: pinyin = "wang " Case -12802 To -12608: pinyin = "wei " Case -12607 To -12598: pinyin = "wen " Case -12597 To -12595: pinyin = "weng " Case -12594 To -12586: pinyin = "wo " Case -12585 To -12557: pinyin = "wu "Case -12556 To -12360: pinyin = "xi "Case -12359 To -12347: pinyin = "xia "Case -12346 To -12321: pinyin = "xian " Case -12320 To -12301: pinyin = "xiang " Case -12300 To -12121: pinyin = "xiao " Case -12120 To -12100: pinyin = "xie " Case -12099 To -12090: pinyin = "xin " Case -12089 To -12075: pinyin = "xing " Case -12074 To -12068: pinyin = "xiong " Case -12067 To -12059: pinyin = "xiu " Case -12058 To -12040: pinyin = "xu " Case -12039 To -11868: pinyin = "xuan " Case -11867 To -11862: pinyin = "xue " Case -11861 To -11848: pinyin = "xun " Case -11847 To -11832: pinyin = "ya " Case -11831 To -11799: pinyin = "yan " Case -11798 To -11782: pinyin = "yang "Case -11604 To -11590: pinyin = "ye " Case -11589 To -11537: pinyin = "yi " Case -11536 To -11359: pinyin = "yin " Case -11358 To -11341: pinyin = "ying " Case -11340 To -11340: pinyin = "yo " Case -11339 To -11325: pinyin = "yong " Case -11324 To -11304: pinyin = "you " Case -11303 To -11098: pinyin = "yu " Case -11097 To -11078: pinyin = "yuan " Case -11077 To -11068: pinyin = "yue " Case -11067 To -11056: pinyin = "yun " Case -11055 To -11053: pinyin = "za " Case -11052 To -11046: pinyin = "zai " Case -11045 To -11042: pinyin = "zan "Case -11038 To -11025: pinyin = "zao " Case -11024 To -11021: pinyin = "ze " Case -11020 To -11020: pinyin = "zei " Case -11019 To -11019: pinyin = "zen " Case -11018 To -11015: pinyin = "zeng " Case -11014 To -10839: pinyin = "zha " Case -10838 To -10833: pinyin = "zhai " Case -10832 To -10816: pinyin = "zhan " Case -10815 To -10801: pinyin = "zhang " Case -10800 To -10791: pinyin = "zhao " Case -10790 To -10781: pinyin = "zhe " Case -10780 To -10765: pinyin = "zhen " Case -10764 To -10588: pinyin = "zheng " Case -10587 To -10545: pinyin = "zhi "Case -10533 To -10520: pinyin = "zhou " Case -10519 To -10332: pinyin = "zhu " Case -10331 To -10330: pinyin = "zhua " Case -10329 To -10329: pinyin = "zhuai " Case -10328 To -10323: pinyin = "zhuan " Case -10322 To -10316: pinyin = "zhuang " Case -10315 To -10310: pinyin = "zhui " Case -10309 To -10308: pinyin = "zhun " Case -10307 To -10297: pinyin = "zhuo " Case -10296 To -10282: pinyin = "zi " Case -10281 To -10275: pinyin = "zong " Case -10274 To -10271: pinyin = "zou " Case -10270 To -10263: pinyin = "zu " Case -10262 To -10261: pinyin = "zuan "Case -10260 To -10257: pinyin = "zui "Case -10256 To -10255: pinyin = "zun " Case -10254 To -10254: pinyin = "zuo "Case Else:pinyin = pEnd SelectEnd FunctionFunction getpy(str)For i = 1 To Len(str)getpy = getpy & pinyin(Mid(str, i, 1)) Next iEnd Function。

汉字转换为拼音的代码

'***************************************************************************'* MODULE NAME: HzToPy'* AUTHOR & DATE: tt.t'* 03 Apirl 2007'* DESCRIPTION: 将中文字符串转换为拼音，就这些～'* 有汉字得到拼音其实并不是我很关心的一个问题，只是发现已经公开'* 的方法有很大的缺陷，但WORD却做得很好，因此才尝试解决这个问题。

'* 过程比我预期的要曲折的多，主要是VBA实在是一种很受限制的语言。

'* 不过好在有Google和Olldbg，难题也仅仅是如何找到绕过限制的途径，'* 终于在5个小时内搞定了一切～'* 时间比我预计的长了很多，因为我实在是不了解VBA，也不很熟悉OLE:"('* 不过好在一切都解决了～～终于从VBA小白成长了一些。

'* 其实VBA也是很强大的～'*'* Theory：废话了好多还是说说原理吧，虽然不是每个人都很关心～'* WORD的拼音向导能够将汉字转成拼音全是倚仗微软拼音的帮助，'* 微软拼音2.0以上版本都提供了汉字到拼音的转换功能。

'* 微软拼音MSIME.China类中的IFELanguage接口具体实现了转换功能'* 不过MSIME.China中没有提供IDispatch接口，VBA的CreateObject 不支持'* 调用这样的类，因此我们只好手工调用。

CoCreateInstance可以创建类'* 并获取IFELanguage接口，但我们无法直接调用，因为VBA不知道如何调用'* IFELanguage接口的Method。

通过SQLServer取出汉字的拼音

SQL函数实现用拼音对汉字模糊查询方法一：create function f_GetPy(@Str nvarchar(400>> returns nvarchar(4000> asbegindeclare @strlen int,@re nvarchar(4000> declare @t table(chr nchar(1> collate Chinese_PRC_CI_AS,letter nchar(1>> insert @t select '吖','A' union all select '八','B' union all select '嚓','C' union all select '咑','D' union all select '妸','E' union all select '发','F' union all select '旮','G' union all select '铪','H' union all select '丌','J' union all select '咔','K' union all select '垃','L' union all select '嘸','M' union all select '拏','N' union all select '噢','O' union all select '妑','P' union all select '七','Q' union all select '呥','R' union all select '仨','S' union all select '他','T' union all select '屲','W' union all select '夕','X' union all select '丫','Y' union all select '帀','Z'select @strlen=len(@str>,@re=''while @strlen>0beginselect top 1 @re=letter+@re,@strlen=@strlen-1from @t a where chr<=substring(@str,@strlen,1>order by chr descif @@rowcount=0select @re=substring(@str,@strlen,1>+@re,@strlen=@strlen-1 endreturn(@re>endgo--测试select dbo.f_GetPy('东莞市'> as 东莞市,dbo.f_GetPy('ab中c 国人'> as 中国人b5E2RGbCAP--以后查询的时候,就可以调用上面的函数来实现汉字模糊查询select * from 表 where dbo.f_getpy(字段>='zgyh'方法二：CREATE FUNCTION f_GetPY(@str nvarchar(4000>>RETURNS nvarchar(4000>ASBEGINDECLARE @py TABLE(ch char(1>,hz1 nchar(1> COLLATE Chinese_PRC_CS_AS_KS_WS,hz2 nchar(1> COLLATE Chinese_PRC_CS_AS_KS_WS>INSERT @py SELECT 'A',N'吖',N'鏊'UNION ALL SELECT 'B',N'八',N'簿'UNION ALL SELECT 'C',N'嚓',N'错'UNION ALL SELECT 'D',N'哒',N'跺'UNION ALL SELECT 'E',N'屙',N'贰'UNION ALL SELECT 'F',N'发',N'馥'UNION ALL SELECT 'G',N'旮',N'过'UNION ALL SELECT 'H',N'铪',N'蠖'UNION ALL SELECT 'J',N'丌',N'竣'UNION ALL SELECT 'K',N'咔',N'廓'UNION ALL SELECT 'L',N'垃',N'雒'UNION ALL SELECT 'M',N'妈',N'穆'UNION ALL SELECT 'N',N'拿',N'糯'UNION ALL SELECT 'O',N'噢',N'沤'UNION ALL SELECT 'P',N'趴',N'曝'UNION ALL SELECT 'Q',N'七',N'群'UNION ALL SELECT 'R',N'蚺',N'箬'UNION ALL SELECT 'S',N'仨',N'锁'UNION ALL SELECT 'T',N'他',N'箨'UNION ALL SELECT 'W',N'哇',N'鋈'UNION ALL SELECT 'X',N'夕',N'蕈'UNION ALL SELECT 'Y',N'丫',N'蕴'UNION ALL SELECT 'Z',N'匝',N'做'DECLARE @i intSET @i=PATINDEX('%[吖-做]%' COLLATEChinese_PRC_CS_AS_KS_WS,@str>WHILE @i>0SELECT @str=REPLACE(@str,SUBSTRING(@str,@i,1>,ch> ,@i=PATINDEX('%[吖-做]%' COLLATEChinese_PRC_CS_AS_KS_WS,@str>FROM @pyWHERE SUBSTRING(@str,@i,1> BETWEEN hz1 AND hz2 RETURN(@str>ENDGO然后，使用查询语句select * from table wheresubstring(hoceedb.dbo.f_GetPY(str>,1,1> = 'A'查出table表中str字段第一个字符的拼音首字母等于‘A’的所有数据/*根据汉字获取全拼1.生成所有读音临时表2.根据Chinese_PRC_CS_AS_KS_WS 排序获取读音*/CREATE function f_GetPy(@str varchar(100>> returns varchar(8000> asbegindeclare @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'兡','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'仓','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'巛','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'扥','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'仒','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'给','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'魧','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'丌','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'尻','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'来','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'寽','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'瓱','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'莻','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'筽','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'氕','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'曲','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'栍','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'兙','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'他','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'旽','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'旴','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'帀','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'抓','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>0beginselect top 1 p1EanqFDPw@re=UPPER(substring(py,1,1> >+substring(py,2,len(py>>+@re,@ strlen=DXDiTa9E3d@strlen-1from @t a where chr<=substring(@str,@strlen,1>order by chr collate Chinese_PRC_CS_AS_KS_WS descif @@rowcount=0select @re=substring(@str,@strlen,1>+@re,@strlen=@strlen-1end return(@re>endRTCrpUDGiT通过SQL Server取出汉字的拼音用排序规则特点计算汉字笔划和取得拼音首字母SQL SERVER的排序规则平时使用不是很多，也许不少初学者还比较陌生，但有一个错误大家应是经常碰到: SQL SERVER数据库，在跨库多表连接查询时，若两数据库默认字符集不同，系统就会返回这样的错误： "无法解决 equal to 操作的排序规则冲突。

利用SQL SERVER实现拼音简码自动转换汉字

ＥＲ方法得到所有汉字的ｅｔｙｉｔ１９８１ｎｏ＃ｅｅｔｏ２９２ｃｄｐｄｎｉ（ｎ，９６，）ｉｔｔｔ
ｆｍｙｃｌｍｎｓｓｏｕｓｂｒｏｓｓｏｕｓａ，ｙｃｌｍｎ
能。
关键词：序规则；ＱＥＶＲ；音转换汉字排ＳＬＳＲＥ拼中图分类号：Ｐ９．２Ｔ３１１文献标识码：Ａ文章编号：６１８４２１）２００－２１７－６（０００－９０７０
孛・・・・・・・・・・・・・・・・・・・夺・｝・・・・・・・・・・・・・・・・・・・・＞・・・・・・夺夺夺夺夺夺牵夺夺．夺夺夺 ÷ 夺夺夺．。夺夺孛孛夺夺夺夺．夺夺夺夺 ÷ 孛夺 ÷ ÷ 夺－夺夺夺夺争４
摘要：在管理系统的开发中如何发挥程序优势，用户汉字数据的输入更加简单，使是程序员面临的一个新课题。本文利用ＳＬＳＲＥＱＥＶＲ的排序规则，通过在ＳＬＳＲＥ中自定义函数实现了拼音简码自动转换汉字的功ＱＥＶＲ
据库，建用户定义的函数如下：创
ＣＲＥＡＴｕｃｉｎｄｏｆｎＥｆｎｔｂ．ｕｏ
＿
ＭＳ对排序规则的描述是：在ＭｉｏｏＱｅｖｒ “ ｃｓｆＳＬＳｒｅｒｔ
中，串的物理存储由排序规则控制。排序规则指定表字符示每个字符的位模式以及存储和比较字符所使用的规

中文转拼音(sql)

USE [master]GO/****** Object: UserDefinedFunction [dbo].[fn_Spell] Script Date: 11/09/2013 10:06:34 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate function [dbo].[fn_Spell](@words nvarchar(2000))returns varchar(8000)asbegindeclare @word nchar(1)declare @pinyin varchar(8000)declare @i intdeclare @words_len intdeclare @unicode intset @i = 1set @words = ltrim(rtrim(@words))set @words_len = len(@words)while (@i <= @words_len) --循环取字符beginset @word = substring(@words, @i, 1)set @unicode = unicode(@word)set @pinyin = ISNULL(@pinyin+space(1),'')+(case when unicode(@word) between 19968 and 19968+20901 then(select top 1 py from (select 'a' as py,N'厑' as wordunion all select 'ai',N'靉'union all select 'an',N'黯'union all select 'ang',N'醠'union all select 'ao',N'驁'union all select 'ba',N'欛'union all select 'bai',N'瓸' --韛兡瓸union all select 'ban',N'瓣'union all select 'bang',N'鎊'union all select 'bao',N'鑤'union all select 'bei',N'鐾'union all select 'ben',N'輽'union all select 'beng',N'鏰'union all select 'bi',N'鼊'union all select 'biao',N'鰾'union all select 'bie',N'彆'union all select 'bin',N'鬢'union all select 'bing',N'靐'union all select 'bo',N'蔔'union all select 'bu',N'簿'union all select 'ca',N'囃'union all select 'cai',N'乲' --縩乲union all select 'can',N'爘'union all select 'cang',N'賶'union all select 'cao',N'鼜'union all select 'ce',N'簎'union all select 'cen',N'笒'union all select 'ceng',N'乽' --硛硳岾猠乽union all select 'cha',N'詫'union all select 'chai',N'囆'union all select 'chan',N'顫'union all select 'chang',N'韔'union all select 'chao',N'觘'union all select 'che',N'爡'union all select 'chen',N'讖'union all select 'cheng',N'秤'union all select 'chi',N'鷘'union all select 'chong',N'銃'union all select 'chou',N'殠'union all select 'chu',N'矗'union all select 'chuai',N'踹'union all select 'chuan',N'鶨'union all select 'chuang',N'愴'union all select 'chui',N'顀'union all select 'chun',N'蠢'union all select 'chuo',N'縒'union all select 'ci',N'嗭' --賜嗭union all select 'cong',N'謥'union all select 'cou',N'輳'union all select 'cu',N'顣'union all select 'cuan',N'爨'union all select 'cui',N'臎'union all select 'cun',N'籿'union all select 'cuo',N'錯'union all select 'da',N'橽'union all select 'dai',N'靆'union all select 'dan',N'饏'union all select 'dao',N'纛'union all select 'de',N'的'union all select 'den',N'扽'union all select 'deng',N'鐙'union all select 'di',N'螮'union all select 'dia',N'嗲'union all select 'dian',N'驔'union all select 'diao',N'鑃'union all select 'die',N'嚸' --眰嚸union all select 'ding',N'顁'union all select 'diu',N'銩'union all select 'dong',N'霘'union all select 'dou',N'鬭'union all select 'du',N'蠹'union all select 'duan',N'叾' --籪叾union all select 'dui',N'譵'union all select 'dun',N'踲'union all select 'duo',N'鵽'union all select 'e',N'鱷'union all select 'en',N'摁'union all select 'eng',N'鞥'union all select 'er',N'樲'union all select 'fa',N'髮'union all select 'fan',N'瀪'union all select 'fang',N'放'union all select 'fei',N'靅'union all select 'fen',N'鱝'union all select 'feng',N'覅'union all select 'fo',N'梻'union all select 'fou',N'鴀'union all select 'fu',N'猤' --鰒猤union all select 'ga',N'魀'union all select 'gai',N'瓂'union all select 'gan',N'灨'union all select 'gang',N'戇'union all select 'gao',N'鋯'union all select 'ge',N'獦'union all select 'gei',N'給'union all select 'gen',N'搄'union all select 'geng',N'堩' --亙堩啹喼嗰union all select 'gong',N'兣' --熕贑兝兣union all select 'gou',N'購'union all select 'gu',N'顧'union all select 'guai',N'恠' union all select 'guan',N'鱹' union all select 'guang',N'撗' union all select 'gui',N'鱥'union all select 'gun',N'謴'union all select 'guo',N'腂'union all select 'ha',N'哈'union all select 'hai',N'饚'union all select 'han',N'鶾'union all select 'hang',N'沆' union all select 'hao',N'兞'union all select 'he',N'靏'union all select 'hei',N'嬒'union all select 'hen',N'恨'union all select 'heng',N'堼' --堼囍union all select 'hong',N'鬨' union all select 'hou',N'鱟'union all select 'hu',N'鸌'union all select 'hua',N'蘳'union all select 'huai',N'蘾' union all select 'huan',N'鰀' union all select 'huang',N'鎤' union all select 'hui',N'顪'union all select 'hun',N'諢'union all select 'huo',N'夻'union all select 'ji',N'驥'union all select 'jia',N'嗧'union all select 'jian',N'鑳' union all select 'jiang',N'謽' union all select 'jiao',N'釂' union all select 'jie',N'繲'union all select 'jin',N'齽'union all select 'jing',N'竸' union all select 'jiong',N'蘔' union all select 'jiu',N'欍'union all select 'ju',N'爠'union all select 'juan',N'羂' union all select 'jue',N'钁'union all select 'jun',N'攈'union all select 'ka',N'鉲'union all select 'kai',N'乫' --鎎乫union all select 'kan',N'矙'union all select 'kang',N'閌'union all select 'ke',N'騍'union all select 'ken',N'褃'union all select 'keng',N'鏗' --巪乬唟厼怾union all select 'kong',N'廤'union all select 'kou',N'鷇'union all select 'ku',N'嚳'union all select 'kua',N'骻'union all select 'kuai',N'鱠'union all select 'kuan',N'窾'union all select 'kuang',N'鑛'union all select 'kui',N'鑎'union all select 'kun',N'睏'union all select 'kuo',N'穒'union all select 'la',N'鞡'union all select 'lai',N'籟'union all select 'lan',N'糷'union all select 'lang',N'唥'union all select 'lao',N'軂'union all select 'le',N'餎'union all select 'lei',N'脷' --嘞脷union all select 'leng',N'睖'union all select 'li',N'瓈'union all select 'lia',N'倆'union all select 'lian',N'纞'union all select 'liang',N'鍄'union all select 'liao',N'瞭'union all select 'lie',N'鱲'union all select 'lin',N'轥' --轥拎union all select 'ling',N'炩'union all select 'liu',N'咯' --瓼甅囖咯union all select 'long',N'贚'union all select 'lou',N'鏤'union all select 'lu',N'氇'union all select 'lv',N'鑢'union all select 'luan',N'亂'union all select 'lue',N'擽'union all select 'lun',N'論'union all select 'luo',N'鱳'union all select 'ma',N'嘛'union all select 'mai',N'霢'union all select 'man',N'蘰'union all select 'mang',N'蠎'union all select 'mao',N'唜'union all select 'me',N'癦' --癦呅union all select 'mei',N'嚜'union all select 'men',N'們'union all select 'meng',N'霥' --霿踎union all select 'mi',N'羃'union all select 'mian',N'麵'union all select 'miao',N'廟'union all select 'mie',N'鱴' --鱴瓱union all select 'min',N'鰵'union all select 'ming',N'詺'union all select 'miu',N'謬'union all select 'mo',N'耱' --耱乮union all select 'mou',N'麰' --麰蟱union all select 'mu',N'旀'union all select 'na',N'魶'union all select 'nai',N'錼'union all select 'nan',N'婻'union all select 'nang',N'齉'union all select 'nao',N'臑'union all select 'ne',N'呢'union all select 'nei',N'焾' --嫩焾union all select 'nen',N'嫩'union all select 'neng',N'能' --莻嗯鈪銰啱union all select 'ni',N'嬺'union all select 'nian',N'艌'union all select 'niang',N'釀'union all select 'niao',N'脲'union all select 'nie',N'钀'union all select 'nin',N'拰'union all select 'ning',N'濘'union all select 'niu',N'靵'union all select 'nong',N'齈'union all select 'nou',N'譳'union all select 'nu',N'搙'union all select 'nv',N'衄'union all select 'nue',N'瘧'union all select 'nuan',N'燶' --硸黁燶郍union all select 'nuo',N'桛'union all select 'o',N'鞰' --毮夞乯鞰union all select 'ou',N'漚'union all select 'pa',N'袙'union all select 'pai',N'磗' --鎃磗union all select 'pan',N'鑻'union all select 'pang',N'胖'union all select 'pei',N'轡'union all select 'pen',N'喯'union all select 'peng',N'喸' --浌巼闏乶喸union all select 'pi',N'鸊'union all select 'pian',N'騙'union all select 'piao',N'慓'union all select 'pie',N'嫳'union all select 'pin',N'聘'union all select 'ping',N'蘋'union all select 'po',N'魄'union all select 'pou',N'哛' --兺哛union all select 'pu',N'曝'union all select 'qi',N'蟿'union all select 'qia',N'髂'union all select 'qian',N'縴'union all select 'qiang',N'瓩' --羻兛瓩union all select 'qiao',N'躈'union all select 'qie',N'籡'union all select 'qin',N'藽'union all select 'qing',N'櫦'union all select 'qiong',N'瓗'union all select 'qiu',N'糗'union all select 'qu',N'覻'union all select 'quan',N'勸'union all select 'que',N'礭'union all select 'qun',N'囕'union all select 'ran',N'橪'union all select 'rang',N'讓'union all select 'rao',N'繞'union all select 're',N'熱'union all select 'ren',N'餁'union all select 'reng',N'陾'union all select 'ri',N'馹'union all select 'rong',N'穃'union all select 'rou',N'嶿'union all select 'ru',N'擩'union all select 'ruan',N'礝'union all select 'rui',N'壡'union all select 'run',N'橍' --橍挼union all select 'ruo',N'鶸'union all select 'sa',N'栍' --櫒栍union all select 'sai',N'虄' --簺虄union all select 'san',N'閐'union all select 'sao',N'髞'union all select 'se',N'飋' --裇聓union all select 'sen',N'篸'union all select 'seng',N'縇' --閪縇union all select 'sha',N'霎'union all select 'shai',N'曬'union all select 'shan',N'鱔'union all select 'shang',N'緔'union all select 'shao',N'潲'union all select 'she',N'欇'union all select 'shen',N'瘮'union all select 'sheng',N'賸'union all select 'shi',N'瓧' --鰘齛兙瓧union all select 'shou',N'鏉'union all select 'shu',N'虪'union all select 'shua',N'誜'union all select 'shuai',N'卛'union all select 'shuan',N'腨'union all select 'shuang',N'灀'union all select 'shui',N'睡'union all select 'shun',N'鬊'union all select 'shuo',N'鑠'union all select 'si',N'乺' --瀃螦乺union all select 'song',N'鎹'union all select 'sou',N'瘶'union all select 'su',N'鷫'union all select 'suan',N'算'union all select 'sui',N'鐩'union all select 'sun',N'潠'union all select 'suo',N'蜶'union all select 'ta',N'襨' --躢襨union all select 'tai',N'燤'union all select 'tan',N'賧'union all select 'tang',N'燙'union all select 'tao',N'畓' --討畓union all select 'te',N'蟘'union all select 'teng',N'朰' --霯唞朰union all select 'ti',N'趯'union all select 'tian',N'舚'union all select 'tiao',N'糶'union all select 'tie',N'餮'union all select 'ting',N'乭' --濎乭union all select 'tong',N'憅'union all select 'tu',N'鵵'union all select 'tuan',N'褖' union all select 'tui',N'駾'union all select 'tun',N'坉'union all select 'tuo',N'籜'union all select 'wa',N'韤'union all select 'wai',N'顡'union all select 'wan',N'贎'union all select 'wang',N'朢' union all select 'wei',N'躛'union all select 'wen',N'璺'union all select 'weng',N'齆' union all select 'wo',N'齷'union all select 'wu',N'鶩'union all select 'xi',N'衋'union all select 'xia',N'鏬'union all select 'xian',N'鼸' union all select 'xiang',N'鱌' union all select 'xiao',N'斆' union all select 'xie',N'躞'union all select 'xin',N'釁'union all select 'xing',N'臖' union all select 'xiong',N'敻' union all select 'xiu',N'齅'union all select 'xu',N'蓿'union all select 'xuan',N'贙' union all select 'xue',N'瀥'union all select 'xun',N'鑂'union all select 'ya',N'齾'union all select 'yan',N'灩'union all select 'yang',N'樣' union all select 'yao',N'鑰'union all select 'ye',N'岃' --鸈膶岃union all select 'yi',N'齸'union all select 'yin',N'檼'union all select 'ying',N'譍' union all select 'yo',N'喲'union all select 'yong',N'醟' union all select 'you',N'鼬'union all select 'yu',N'爩'union all select 'yuan',N'願' union all select 'yue',N'鸙'union all select 'yun',N'韻'union all select 'zai',N'縡'union all select 'zan',N'饡'union all select 'zang',N'臟'union all select 'zao',N'竈'union all select 'ze',N'稄'union all select 'zei',N'鱡'union all select 'zen',N'囎'union all select 'zeng',N'贈'union all select 'zha',N'醡'union all select 'zhai',N'瘵'union all select 'zhan',N'驏'union all select 'zhang',N'瞕'union all select 'zhao',N'羄'union all select 'zhe',N'鷓'union all select 'zhen',N'黮'union all select 'zheng',N'證'union all select 'zhi',N'豒'union all select 'zhong',N'諥'union all select 'zhou',N'驟'union all select 'zhu',N'鑄'union all select 'zhua',N'爪'union all select 'zhuai',N'跩'union all select 'zhuan',N'籑'union all select 'zhuang',N'戅'union all select 'zhui',N'鑆'union all select 'zhun',N'稕'union all select 'zhuo',N'籱'union all select 'zi',N'漬' --漬唨union all select 'zong',N'縱'union all select 'zou',N'媰'union all select 'zu',N'謯'union all select 'zuan',N'攥'union all select 'zui',N'欈'union all select 'zun',N'銌'union all select 'zuo',N'咗') twhere word >= @word collate Chinese_PRC_CS_AS_KS_WS order by word ASC) else @word end)set @i = @i + 1endreturn replace(@pinyin,' ','')ENDGO。

sql_server自动生成拼音码

sql server自动生成拼音首字母的函数建立一个查询，执行下面的语句生成函数fn_GetPy--生成拼音首码CREATE function getInputstr(@str nvarchar(4000)) returns nvarchar(4000)--WITH ENCRYPTIONasbegindeclare @intLen intdeclare @strRet nvarchar(4000)declare @temp nvarchar(100)set @intLen = len(@str)set @strRet = ''while @intLen > 0beginset @temp = ''select @temp = casewhen 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 + @strRetset @intLen = @intLen - 1endreturn lower(@strRet)end。

SQL自定义函数：由汉字生成拼音全拼

SQL自定义函数：由汉字生成拼音全拼SQL自定义函数：由汉字生成拼音全拼CREATE function [dbo].[fun_GetPy_quanpin](@str varchar(100))returns varchar(8000)asbegindeclare @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'辪','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'抽','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'仏','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'乤','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'艽','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'夸','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'驴','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'乮','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'奴','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'氕','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'呥','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'山','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'襨','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'翁','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'佣','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 intselect @strlen=len(@str),@re=''while @strlen>0beginselect top 1 @re=UPPER(substring(py,1,1) )+substring(py,2,len(py))+@re,@s trlen=@strlen-1from @t a where chr<=substring(@str,@strlen,1)order by chr collate Chinese_PRC_CS_AS_KS_WS descif @@rowcount=0select@re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1 endreturn(@re) end。

sqlserver汉字转拼音函数

SQL Server 汉字转拼音函数实战应用一、SQL Server 汉字转拼音函数概述SQL Server 自带的汉字转拼音函数名为“NVARCHART(MAX) CONVERT(VARCHAR(10), 争议汉字，120)”。

该函数可以将一个汉字转换为拼音，返回值为一个 VARCHAR 类型的数据。

其中，争议汉字是该汉字的拼音，120 表示将汉字转换为拼音后最多保留 120 个字符。

二、SQL Server 汉字转拼音函数的使用方法SQL Server 汉字转拼音函数的使用方法非常简单，只需要在查询中使用即可。

假设我们有一个名为“user_info”的表，其中包含用户名和拼音两个字段，如下所示:```-- 创建一个用户信息表CREATE TABLE user_info (username VARCHAR(50) NOT NULL,pinyin VARCHAR(50) NOT NULL);```然后，我们可以使用 SQL Server 汉字转拼音函数将一个汉字转换为拼音，并将其存储到拼音字段中，示例代码如下:```-- 查询用户名并将其转换为拼音SELECT CONVERT(VARCHAR(10), username, 120) AS pinyinFROM user_info;```上述查询将返回所有用户名的拼音，存储在名为“pinyin”的VARCHAR 类型字段中。

三、SQL Server 汉字转拼音函数的实战应用SQL Server 汉字转拼音函数的实战应用非常广泛。

例如，我们可以使用该函数将用户信息表中的用户名转换为拼音，方便用户进行拼音搜索和查询。

假设我们有一个名为“user_info”的表，其中包含用户名、年龄和性别三个字段，如下所示:```-- 创建一个用户信息表CREATE TABLE user_info (username VARCHAR(50) NOT NULL,age INT NOT NULL,gender VARCHAR(10) NOT NULL);```然后，我们可以使用 SQL Server 汉字转拼音函数将一个汉字转换为拼音，并将其存储到拼音字段中，示例代码如下:```-- 查询用户名并将其转换为拼音SELECT CONVERT(VARCHAR(10), username, 120) AS pinyin,username,age,genderFROM user_info;```上述查询将返回所有用户名的拼音、用户名、年龄和性别信息，存储在名为“pinyin”、“username”、“age”和“gender”的四个 VARCHAR 类型字段中。

通过SQL Server取出汉字的拼音

通过SQL Server取出汉字的拼音用排序规则特点计算汉字笔划和取得拼音首字母SQL SERVER的排序规则平时使用不是很多，也许不少初学者还比较陌生，但有一个错误大家应是经常碰到: SQL SERVER数据库，在跨库多表连接查询时，若两数据库默认字符集不同，系统就会返回这样的错误："无法解决 equal to 操作的排序规则冲突。

"一.错误分析：这个错误是因为排序规则不一致造成的，我们做个测试，比如：create table #t1(name varchar(20) collate Albanian_CI_AI_WS,value int)create table #t2(name varchar(20) collate Chinese_PRC_CI_AI_WS,value int )表建好后，执行连接查询：select * from #t1 A inner join #t2 B on =这样，错误就出现了：服务器: 消息 446，级别 16，状态 9，行 1无法解决 equal to 操作的排序规则冲突。

要排除这个错误，最简单方法是，表连接时指定它的排序规则，这样错误就不再出现了。

语句这样写：select *from #t1 A inner join #t2 Bon = collate Chinese_PRC_CI_AI_WS二.排序规则简介：什么叫排序规则呢？MS是这样描述的："在 Microsoft SQL Server 2000 中，字符串的物理存储由排序规则控制。

排序规则指定表示每个字符的位模式以及存储和比较字符所使用的规则。

"在查询分析器内执行下面语句，可以得到SQL SERVER支持的所有排序规则。

select * from ::fn_helpcollations()排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。

如：Chinese_PRC_CS_AI_WS前半部份：指UNICODE字符集，Chinese_PRC_指针对大陆简体字UNICODE的排序规则。

基于SQL Server的中文拼音首字母转化的程序实现

假若拿出任何一个汉字，在挑选出的这些放
汉字中间，用数据库进行一次使用ＧＢ字符利Ｋ集的排序，能够根据这个汉字排列的相对位置便
得到其拼音首字母。
１７完自岭昧帛阱卑哦睥甜蜩睛皑晶３００巴眦
表ｓｅｔ￥，后，改ｓｅｔｈｅ１然修ｈｅ１￥表结构，增加再列—— 站名首字母，用来存储第一列数据的命名转化结果。
一
图３数据显示
收稿日期：０５１ —７２０ — ２０
作者简介：￣姚
（９１）男。１７－，天津人．中国人民武装警察部队学院讲师．工学硕士，主要从事信息技术研究．
０１２３４５６７８９ＡＢＣＥＦＤ
１４癌痨瘌癀瘢瘾瘛痫癀瘸瘢癔瘢瘫瘤瘸５００
１５瘟痛瘰瘭｛瘸瘸瘙癣瘕癌癃癌癞瘸癣３００ｊＩＩｆ
１６瘿瘛癣瘫癫癀瘴瘸犟墅凳磷自皂卑３００【Ｊ
维普资讯
ｌ８
长春工业大学学报（自然科学版）
第２７卷
２用转化函数实现汉字的拼音首字母转化
在使用中文字符集的数据库中，当对一列中文数据使用ｏｄｒｂｒｅｙ排序时，序的结果正是按排进行排列
的，据库内部以中文字符集ＧＢ为例，看一数Ｋ查

SQLSERVER中的汉字转拼音-实现根据拼音搜索汉字【转】

SQLSERVER中的汉字转拼⾳-实现根据拼⾳搜索汉字【转】在项⽬中，有时需要根据拼⾳来搜索对应的汉字，找了许多资料，也参考了⼀些⽰例，这个sql函数总是解决了这个问题，⽽且性能是找的资料中最好的。

原理是通过这个函数将中⽂汉字的字段中的数据转换成拼⾳，再根据拼⾳来进⾏搜索。

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <lisky.li>-- Create date: <2013>-- Description: <汉字转全拼⾳函数>-- =============================================create function [dbo].[Get_QuanPin_li](@str varchar(120))returns varchar(8000)asbegin declare @re varchar(8000),@crs varchar(10) declare @strlen intselect @strlen=len(@str),@re='' while @strlen>0 begin set @crs= substring(@str,@strlen,1) select @re= case when @crs<'吖' then @crs when @crs<='厑' then 'a' when @crs<='靉' then 'ai' when @crs<='黯' then 'an' when @crs<='醠' then 'ang' when @crs<='驁' then 'ao' when @crs<='欛' then 'ba' when @crs<='瓸' then 'bai' when @crs<='瓣' then 'ban' when@crs<='鎊' then 'bang' when @crs<='鑤' then 'bao' when @crs<='鐾' then 'bei' when @crs<='輽' then 'ben' when @crs<='鏰' then 'beng' when @crs<='鼊' then 'bi' when @crs<='變' then 'bian' when @crs<='鰾' then 'biao' when @crs<='彆' then'bie' when @crs<='鬢' then 'bin' when @crs<='靐' then 'bing' when @crs<='蔔' then 'bo' when @crs<='簿' then 'bu' when @crs<='囃' then 'ca' when @crs<='乲' then 'cai' when @crs<='爘' then 'can' when @crs<='賶' then 'cang' when @crs<='鼜' then 'cao' when @crs<='簎' then 'ce' when @crs<='笒' then 'cen' when @crs<='乽' then 'ceng' when @crs<='詫' then'cha' when @crs<='囆' then 'chai' when @crs<='顫' then 'chan' when @crs<='韔' then 'chang' when @crs<='觘' then'chao' when @crs<='爡' then 'che' when @crs<='讖' then 'chen' when @crs<='秤' then 'cheng' when @crs<='鷘' then'chi' when @crs<='銃' then 'chong' when @crs<='殠' then 'chou' when @crs<='矗' then 'chu' when @crs<='踹' then'chuai' when @crs<='鶨' then 'chuan' when @crs<='愴' then 'chuang' when @crs<='顀' then 'chui' when @crs<='蠢' then'chun' when @crs<='縒' then 'chuo' when @crs<='嗭' then 'ci' when @crs<='謥' then 'cong' when @crs<='輳' then 'cou' when @crs<='顣' then 'cu' when @crs<='爨' then 'cuan' when @crs<='臎' then 'cui' when @crs<='籿' then 'cun' when@crs<='錯' then 'cuo' when @crs<='橽' then 'da' when @crs<='靆' then 'dai' when @crs<='饏' then 'dan' when @crs<='闣' then 'dang' when @crs<='纛' then 'dao' when @crs<='的' then 'de' when @crs<='扽' then 'den' when @crs<='鐙' then'deng' when @crs<='螮' then 'di' when @crs<='嗲' then 'dia' when @crs<='驔' then 'dian' when @crs<='鑃' then 'diao' when @crs<='嚸' then 'die' when @crs<='顁' then 'ding' when @crs<='銩' then 'diu' when @crs<='霘' then 'dong' when@crs<='鬭' then 'dou' when @crs<='蠹' then 'du' when @crs<='叾' then 'duan' when @crs<='譵' then 'dui' when @crs<='踲' then 'dun' when @crs<='鵽' then 'duo' when @crs<='鱷' then 'e' when @crs<='摁' then 'en' when @crs<='鞥' then 'eng' when @crs<='樲' then 'er' when @crs<='髮' then 'fa' when @crs<='瀪' then 'fan' when @crs<='放' then 'fang' when@crs<='靅' then 'fei' when @crs<='鱝' then 'fen' when @crs<='覅' then 'feng' when @crs<='梻' then 'fo' when @crs<='鴀' then 'fou' when @crs<='猤' then 'fu' when @crs<='魀' then 'ga' when @crs<='瓂' then 'gai' when @crs<='灨' then 'gan' when @crs<='戇' then 'gang' when @crs<='鋯' then 'gao' when @crs<='獦' then 'ge' when @crs<='給' then 'gei' when@crs<='搄' then 'gen' when @crs<='堩' then 'geng' when @crs<='兣' then 'gong' when @crs<='購' then 'gou' when@crs<='顧' then 'gu' when @crs<='詿' then 'gua' when @crs<='恠' then 'guai' when @crs<='鱹' then 'guan' when @crs<='撗' then 'guang' when @crs<='鱥' then 'gui' when @crs<='謴' then 'gun' when @crs<='腂' then 'guo' when @crs<='哈' then'ha' when @crs<='饚' then 'hai' when @crs<='鶾' then 'han' when @crs<='沆' then 'hang' when @crs<='兞' then 'hao' when @crs<='靏' then 'he' when @crs<='嬒' then 'hei' when @crs<='恨' then 'hen' when @crs<='堼' then 'heng' when@crs<='鬨' then 'hong' when @crs<='鱟' then 'hou' when @crs<='鸌' then 'hu' when @crs<='蘳' then 'hua' when @crs<='蘾' then 'huai' when @crs<='鰀' then 'huan' when @crs<='鎤' then 'huang' when @crs<='顪' then 'hui' when @crs<='諢' then'hun' when @crs<='夻' then 'huo' when @crs<='驥' then 'ji' when @crs<='嗧' then 'jia' when @crs<='鑳' then 'jian' when @crs<='謽' then 'jiang' when @crs<='釂' then 'jiao' when @crs<='繲' then 'jie' when @crs<='齽' then 'jin' when @crs<='竸' then 'jing' when @crs<='蘔' then 'jiong' when @crs<='欍' then 'jiu' when @crs<='爠' then 'ju' when @crs<='羂' then 'juan' when @crs<='钁' then 'jue' when @crs<='攈' then 'jun' when @crs<='鉲' then 'ka' when @crs<='乫' then 'kai' when@crs<='矙' then 'kan' when @crs<='閌' then 'kang' when @crs<='鯌' then 'kao' when @crs<='騍' then 'ke' when @crs<='褃' then 'ken' when @crs<='鏗' then 'keng' when @crs<='廤' then 'kong' when @crs<='鷇' then 'kou' when @crs<='嚳' then'ku' when @crs<='骻' then 'kua' when @crs<='鱠' then 'kuai' when @crs<='窾' then 'kuan' when @crs<='鑛' then 'kuang' when @crs<='鑎' then 'kui' when @crs<='睏' then 'kun' when @crs<='穒' then 'kuo' when @crs<='鞡' then 'la' when@crs<='籟' then 'lai' when @crs<='糷' then 'lan' when @crs<='唥' then 'lang' when @crs<='軂' then 'lao' when @crs<='餎' then 'le' when @crs<='脷' then 'lei' when @crs<='睖' then 'leng' when @crs<='瓈' then 'li' when @crs<='倆' then 'lia' when @crs<='纞' then 'lian' when @crs<='鍄' then 'liang' when @crs<='瞭' then 'liao' when @crs<='鱲' then 'lie' when@crs<='轥' then 'lin' when @crs<='炩' then 'ling' when @crs<='咯' then 'liu' when @crs<='贚' then 'long' when @crs<='鏤' then 'lou' when @crs<='氇' then 'lu' when @crs<='鑢' then 'lv' when @crs<='亂' then 'luan' when @crs<='擽' then 'lue'when @crs<='論' then 'lun' when @crs<='鱳' then 'luo' when @crs<='嘛' then 'ma' when @crs<='霢' then 'mai' when@crs<='蘰' then 'man' when @crs<='蠎' then 'mang' when @crs<='唜' then 'mao' when @crs<='癦' then 'me' when@crs<='嚜' then 'mei' when @crs<='們' then 'men' when @crs<='霥' then 'meng' when @crs<='羃' then 'mi' when @crs<='麵' then 'mian' when @crs<='廟' then 'miao' when @crs<='鱴' then 'mie' when @crs<='鰵' then 'min' when @crs<='詺' then'ming' when @crs<='謬' then 'miu' when @crs<='耱' then 'mo' when @crs<='麰' then 'mou' when @crs<='旀' then 'mu' when @crs<='魶' then 'na' when @crs<='錼' then 'nai' when @crs<='婻' then 'nan' when @crs<='齉' then 'nang' when@crs<='臑' then 'nao' when @crs<='呢' then 'ne' when @crs<='焾' then 'nei' when @crs<='嫩' then 'nen' when @crs<='能' then 'neng' when @crs<='嬺' then 'ni' when @crs<='艌' then 'nian' when @crs<='釀' then 'niang' when @crs<='脲' then'niao' when @crs<='钀' then 'nie' when @crs<='拰' then 'nin' when @crs<='濘' then 'ning' when @crs<='靵' then 'niu' when @crs<='齈' then 'nong' when @crs<='譳' then 'nou' when @crs<='搙' then 'nu' when @crs<='衄' then 'nv' when@crs<='瘧' then 'nue' when @crs<='燶' then 'nuan' when @crs<='桛' then 'nuo' when @crs<='鞰' then 'o' when @crs<='漚' then 'ou' when @crs<='袙' then 'pa' when @crs<='磗' then 'pai' when @crs<='鑻' then 'pan' when @crs<='胖' then 'pang' when @crs<='礮' then 'pao' when @crs<='轡' then 'pei' when @crs<='喯' then 'pen' when @crs<='喸' then 'peng' when@crs<='鸊' then 'pi' when @crs<='騙' then 'pian' when @crs<='慓' then 'piao' when @crs<='嫳' then 'pie' when @crs<='聘' then 'pin' when @crs<='蘋' then 'ping' when @crs<='魄' then 'po' when @crs<='哛' then 'pou' when @crs<='曝' then 'pu' when @crs<='蟿' then 'qi' when @crs<='髂' then 'qia' when @crs<='縴' then 'qian' when @crs<='瓩' then 'qiang' when@crs<='躈' then 'qiao' when @crs<='籡' then 'qie' when @crs<='藽' then 'qin' when @crs<='櫦' then 'qing' when @crs<='瓗' then 'qiong' when @crs<='糗' then 'qiu' when @crs<='覻' then 'qu' when @crs<='勸' then 'quan' when @crs<='礭' then'que' when @crs<='囕' then 'qun' when @crs<='橪' then 'ran' when @crs<='讓' then 'rang' when @crs<='繞' then 'rao' when @crs<='熱' then 're' when @crs<='餁' then 'ren' when @crs<='陾' then 'reng' when @crs<='馹' then 'ri' when@crs<='穃' then 'rong' when @crs<='嶿' then 'rou' when @crs<='擩' then 'ru' when @crs<='礝' then 'ruan' when @crs<='壡' then 'rui' when @crs<='橍' then 'run' when @crs<='鶸' then 'ruo' when @crs<='栍' then 'sa' when @crs<='虄' then 'sai' when @crs<='閐' then 'san' when @crs<='喪' then 'sang' when @crs<='髞' then 'sao' when @crs<='飋' then 'se' when@crs<='篸' then 'sen' when @crs<='縇' then 'seng' when @crs<='霎' then 'sha' when @crs<='曬' then 'shai' when@crs<='鱔' then 'shan' when @crs<='緔' then 'shang' when @crs<='潲' then 'shao' when @crs<='欇' then 'she' when@crs<='瘮' then 'shen' when @crs<='賸' then 'sheng' when @crs<='瓧' then 'shi' when @crs<='鏉' then 'shou' when@crs<='虪' then 'shu' when @crs<='誜' then 'shua' when @crs<='卛' then 'shuai' when @crs<='腨' then 'shuan' when@crs<='灀' then 'shuang' when @crs<='睡' then 'shui' when @crs<='鬊' then 'shun' when @crs<='鑠' then 'shuo' when@crs<='乺' then 'si' when @crs<='鎹' then 'song' when @crs<='瘶' then 'sou' when @crs<='鷫' then 'su' when @crs<='算' then 'suan' when @crs<='鐩' then 'sui' when @crs<='潠' then 'sun' when @crs<='蜶' then 'suo' when @crs<='襨' then 'ta' when @crs<='燤' then 'tai' when @crs<='賧' then 'tan' when @crs<='燙' then 'tang' when @crs<='畓' then 'tao' when@crs<='蟘' then 'te' when @crs<='朰' then 'teng' when @crs<='趯' then 'ti' when @crs<='舚' then 'tian' when @crs<='糶' then 'tiao' when @crs<='餮' then 'tie' when @crs<='乭' then 'ting' when @crs<='憅' then 'tong' when @crs<='透' then 'tou' when @crs<='鵵' then 'tu' when @crs<='褖' then 'tuan' when @crs<='駾' then 'tui' when @crs<='坉' then 'tun' when@crs<='籜' then 'tuo' when @crs<='韤' then 'wa' when @crs<='顡' then 'wai' when @crs<='贎' then 'wan' when @crs<='朢' then 'wang' when @crs<='躛' then 'wei' when @crs<='璺' then 'wen' when @crs<='齆' then 'weng' when @crs<='齷' then'wo' when @crs<='鶩' then 'wu' when @crs<='衋' then 'xi' when @crs<='鏬' then 'xia' when @crs<='鼸' then 'xian' when @crs<='鱌' then 'xiang' when @crs<='斆' then 'xiao' when @crs<='躞' then 'xie' when @crs<='釁' then 'xin' when @crs<='臖' then 'xing' when @crs<='敻' then 'xiong' when @crs<='齅' then 'xiu' when @crs<='蓿' then 'xu' when @crs<='贙' then'xuan' when @crs<='瀥' then 'xue' when @crs<='鑂' then 'xun' when @crs<='齾' then 'ya' when @crs<='灩' then 'yan' when @crs<='樣' then 'yang' when @crs<='鑰' then 'yao' when @crs<='岃' then 'ye' when @crs<='齸' then 'yi' when@crs<='檼' then 'yin' when @crs<='譍' then 'ying' when @crs<='喲' then 'yo' when @crs<='醟' then 'yong' when @crs<='鼬' then 'you' when @crs<='爩' then 'yu' when @crs<='願' then 'yuan' when @crs<='鸙' then 'yue' when @crs<='韻' then'yun' when @crs<='雥' then 'za' when @crs<='縡' then 'zai' when @crs<='饡' then 'zan' when @crs<='臟' then 'zang' when @crs<='竈' then 'zao' when @crs<='稄' then 'ze' when @crs<='鱡' then 'zei' when @crs<='囎' then 'zen' when@crs<='贈' then 'zeng' when @crs<='醡' then 'zha' when @crs<='瘵' then 'zhai' when @crs<='驏' then 'zhan' when@crs<='瞕' then 'zhang' when @crs<='羄' then 'zhao' when @crs<='鷓' then 'zhe' when @crs<='黮' then 'zhen' when@crs<='證' then 'zheng' when @crs<='豒' then 'zhi' when @crs<='諥' then 'zhong' when @crs<='驟' then 'zhou' when@crs<='鑄' then 'zhu' when @crs<='⽖' then 'zhua' when @crs<='跩' then 'zhuai' when @crs<='籑' then 'zhuan' when@crs<='戅' then 'zhuang' when @crs<='鑆' then 'zhui' when @crs<='稕' then 'zhun' when @crs<='籱' then 'zhuo' when@crs<='漬' then 'zi' when @crs<='縱' then 'zong' when @crs<='媰' then 'zou' when @crs<='謯' then 'zu' when @crs<='攥' then 'zuan' when @crs<='欈' then 'zui' when @crs<='銌' then 'zun' when @crs<='咗' then 'zuo' else @crs end+'end return(@re)endGO例如：select Name, dbo.Get_QuanPin_li(Name) from tableName where (dbo.Get_QuanPin_li(Name) like'%zhongguo%' or Namelike'%zhongguo%')。

SQL汉字转拼音

unionallselect'cou',N'輳'
unionallselect'cu',N'顣'
unionallselect'cuan',N'爨'
unionallselect'cui',N'臎'
unionallselect'cun',N'籿'
unionallselect'cuo',N'錯'
unionallselect'da',N'橽'
unionallselect'chi',N'鷘'
unionallselect'chong',N'銃'
unionallselect'chou',N'殠'
unionallselect'chu',N'矗'
unionallselect'chuai',N'踹'
unionallselect'chuan',N'鶨'
unionallselect'duo',N'鵽'
unionallselect'e',N'鱷'
unionallselect'en',N'摁'
unionallselect'eng',N'鞥'
unionallselect'er',N'樲'

sql汉字转拼音

sql汉字转拼⾳/*创建取拼⾳⾸字母函数*/create function [dbo].[fn_ChineseToSpell](@strChinese varchar(500)='')returns varchar(500)asbegin /*函数实现开始*/declare @strLen int,@return varchar(500),@i intdeclare @n int,@c char(1),@chn nchar(1)select @strLen=len(@strChinese),@return='',@i=0while @i<@strLenbegin /*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 belseset @c=@chnset @return=@return+@cend /*while循环结束*/return(@return)end /*函数实现结束*/select [dbo].[fn_ChineseToSpell]('⽔管')。

sql函数汉字转拼音

sql函数汉字转拼⾳GO/****** Object: UserDefinedFunction [dbo].[fn_GetPy] Script Date: 2017/1/4 10:53:49 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER function [dbo].[fn_GetPy](@str nvarchar(4000))returns nvarchar(4000)　--⽤于加密--WITH ENCRYPTIONasbegindeclare @intLen intdeclare @strRet nvarchar(4000)declare @temp nvarchar(100)set @intLen = len(@str)set @strRet = ''while @intLen > 0beginset @temp = ''select @temp = casewhen 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 + @strRetset @intLen = @intLen - 1endreturn lower(@strRet)end 效果如下：。

通过SQLServer取出汉字的拼音

通过SQL Server 取出汉字的拼音用排序规则特点计算汉字笔划和取得拼音首字母SQL SERVER勺排序规则平时使用不是很多，也许不少初学者还比较陌生，但有一个错误大家应是经常碰到：SQL SERVER数据库，在跨库多表连接查询时，若两数据库默认字符集不同，系统就会返回这样勺错误：" 无法解决equal to 操作勺排序规则冲突。

"一. 错误分析：这个错误是因为排序规则不一致造成勺，我们做个测试，比如：create table #t1( name varchar(20) collate Albanian_CI_AI_WS, value int)create table #t2( name varchar(20) collate Chinese_PRC_CI_AI_WS, value int )表建好后，执行连接查询：select * from #t1 A inner join #t2 B on = 这样，错误就出现了：服务器：消息446 ，级别16 ，状态9 ，行1 无法解决equal to操作勺排序规则冲突。

要排除这个错误，最简单方法是，表连接时指定它勺排序规则，这样错误就不再出现了。

语句这样写：select * from #t1 A inner join #t2 B on = collate Chinese_PRC_CI_AI_WS二.排序规则简介：什么叫排序规则呢？MS是这样描述的："在Microsoft SQL Server 2000 中, 字符串勺物理存储由排序规则控制。

排序规则指定表示每个字符勺位模式以及存储和比较字符所使用的规则。

"在查询分析器内执行下面语句，可以得到SQL SERVE支持的所有排序规则。

select * from ：：fn_helpcollations()排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。

如：Chinese_PRC_CS_AI_WS前半部份：指UNICODE?符集，Chinese_PRCj f针对大陆简体字UNICODE勺排序规则。

sql语句汉字转拼音首字母

sql语句汉字转拼⾳⾸字母(1)-------------------------------------------------------------------------------------------------------------------------------------------------------------------------create function GetPY(@str varchar(500))returns varchar(500)asbegindeclare @cyc int,@length int,@str1 varchar(100),@charcate varbinary(20)set @cyc=1--从第⼏个字开始取set @length=len(@str)--输⼊汉字的长度set @str1=''--⽤于存放返回值while @cyc<=1beginselect @charcate=cast(substring(@str,@cyc,1) as varbinary)--每次取出⼀个字并将其转变成⼆进制,便于与GBK编码表进⾏⽐较if @charcate>=0XB0A1 and @charcate<=0XB0C4set @str1=@str1+'A'--说明此汉字的⾸字母为A,以下同上else if @charcate>=0XB0C5 and @charcate<=0XB2C0set @str1=@str1+'B'else if @charcate>=0XB2C1 and @charcate<=0XB4EDset @str1=@str1+'C'else if @charcate>=0XB4EE and @charcate<=0XB6E9set @str1=@str1+'D'else if @charcate>=0XB6EA and @charcate<=0XB7A1set @str1=@str1+'E'else if @charcate>=0XB7A2 and @charcate<=0XB8C0set @str1=@str1+'F'else if @charcate>=0XB8C1 and @charcate<=0XB9FDset @str1=@str1+'G'else if @charcate>=0XB9FE and @charcate<=0XBBF6set @str1=@str1+'H'else if @charcate>=0XBBF7 and @charcate<=0XBFA5set @str1=@str1+'J'else if @charcate>=0XBFA6 and @charcate<=0XC0ABset @str1=@str1+'K'else if @charcate>=0XC0AC and @charcate<=0XC2E7set @str1=@str1+'L'else if @charcate>=0XC2E8 and @charcate<=0XC4C2set @str1=@str1+'M'else if @charcate>=0XC4C3 and @charcate<=0XC5B5set @str1=@str1+'N'else if @charcate>=0XC5B6 and @charcate<=0XC5BDset @str1=@str1+'O'else if @charcate>=0XC5BE and @charcate<=0XC6D9set @str1=@str1+'P'else if @charcate>=0XC6DA and @charcate<=0XC8BAset @str1=@str1+'Q'else if @charcate>=0XC8BB and @charcate<=0XC8F5set @str1=@str1+'R'else if @charcate>=0XC8F6 and @charcate<=0XCBF9set @str1=@str1+'S'else if @charcate>=0XCBFA and @charcate<=0XCDD9set @str1=@str1+'T'else if @charcate>=0XCDDA and @charcate<=0XCEF3set @str1=@str1+'W'else if @charcate>=0XCEF4 and @charcate<=0XD1B8set @str1=@str1+'X'else if @charcate>=0XD1B9 and @charcate<=0XD4D0set @str1=@str1+'Y'else if @charcate>=0XD4D1 and @charcate<=0XD7F9set @str1=@str1+'Z'set @cyc=@cyc+1--取出输⼊汉字的下⼀个字endreturn @str1--返回输⼊汉字的⾸字母end(2)--------------------------------------------------------------------------------------------------------------------------------------------Create function fun_getPY ( @str nvarchar(4000) )returns nvarchar(4000)asbegindeclare @word nchar(1),@PY nvarchar(4000)set @PY=''while len(@str)>0beginset @word=left(@str,1)--如果⾮汉字字符，返回原字符set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901 then (select top 1 PYfrom(select 'A' as PY,N'驁' as wordunion all select 'B',N'簿'union all select 'C',N'錯'union all select 'D',N'鵽'union all select 'E',N'樲'union all select 'F',N'鰒'union all select 'G',N'腂'union all select 'H',N'夻'union all select 'J',N'攈'union all select 'K',N'穒'union all select 'L',N'鱳'union all select 'M',N'旀'union all select 'N',N'桛'union all select 'O',N'漚'union all select 'P',N'曝'union all select 'Q',N'囕'union all select 'R',N'鶸'union all select 'S',N'蜶'union all select 'T',N'籜'union all select 'W',N'鶩'union all select 'X',N'鑂'union all select 'Y',N'韻'union all select 'Z',N'咗') Twhere word>=@word collate Chinese_PRC_CS_AS_KS_WSorder by PY ASC)else @wordend)set @str=right(@str,len(@str)-1)endreturn @PYend。

Mysql中文汉字转拼音的实现（每个汉字转换全拼）

Mysql中⽂汉字转拼⾳的实现（每个汉字转换全拼）-- 创建汉字拼⾳对照临时表CREATE TABLE IF NOT EXISTS `t_base_pinyin` (`pin_yin_` varchar(255) CHARACTER SET gbk NOT NULL,`code_` int(11) NOT NULL,PRIMARY KEY (`code_`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;⼆、插⼊对照数据复制代码代码如下:-- 插⼊数据INSERT INTO t_base_pinyin (pin_yin_,code_) VALUES ("a", 20319),("ai", 20317),("an", 20304),("ang", 20295),("ao", 20292),("ba", 20283),("bai", 20265),("ban", 20257),("bang", 20242),("bao", 20230),("bei", 20051),("ben", 20036),("beng", 20032),(" 三、创建汉字转换拼⾳函数复制代码代码如下:-- 建⽴汉字转换拼⾳函数DROP FUNCTION IF EXISTS to_pinyin;DELIMITER $CREATE FUNCTION to_pinyin(NAME VARCHAR(255) CHARSET gbk)RETURNS VARCHAR(255) CHARSET gbkBEGINDECLARE mycode INT;DECLARE tmp_lcode VARCHAR(2) CHARSET gbk;DECLARE lcode INT;DECLARE tmp_rcode VARCHAR(2) CHARSET gbk;DECLARE rcode INT;DECLARE mypy VARCHAR(255) CHARSET gbk DEFAULT '';DECLARE lp INT;SET mycode = 0;SET lp = 1;SET NAME = HEX(NAME);WHILE lp < LENGTH(NAME) DOSET tmp_lcode = SUBSTRING(NAME, lp, 2);SET lcode = CAST(ASCII(UNHEX(tmp_lcode)) AS UNSIGNED);SET tmp_rcode = SUBSTRING(NAME, lp + 2, 2);SET rcode = CAST(ASCII(UNHEX(tmp_rcode)) AS UNSIGNED);IF lcode > 128 THENSET mycode =65536 - lcode * 256 - rcode ;SELECT CONCAT(mypy,pin_yin_) INTO mypy FROM t_base_pinyin WHERE CODE_ >= ABS(mycode) ORDER BY CODE_ ASC LIMIT 1;SET lp = lp + 4;ELSESET mypy = CONCAT(mypy,CHAR(CAST(ASCII(UNHEX(SUBSTRING(NAME, lp, 2))) AS UNSIGNED)));SET lp = lp + 2;END IF;END WHILE;RETURN LOWER(mypy);END;$DELIMITER ;四、使⽤⽅法代码如下:select to_pinyin('测试')输出结果：to_pinyin('测试')ceshi。

中文转拼音——精选推荐

中⽂转拼⾳以下这个类是个⼯具类,⽤来实现中⽂转拼⾳：public class LanguageTransform{/// <summary>/// 汉字转换成全拼的拼⾳/// </summary>/// <param name="Chstr">汉字字符串</param>/// <returns>转换后的拼⾳字符串</returns>public string StrConvertToPinyin(string Chstr){return GetPinyin(Chstr);}/// <summary>/// 汉字转换成全拼的拼⾳/// </summary>/// <param name="Chstr">汉字字符串</param>/// <param name="IsLoweCase">是否⼩写(默认⼩写即false)</param>/// <param name="Interval">拼⾳之间的间隔(默认⽆间隔)</param>/// <returns>转换后的拼⾳字符串</returns>public string StrConvertToPinyin(string Chstr, bool IsLoweCase = false, string Interval = "") {return GetPinyin(Chstr, IsLoweCase, Interval);}#region 获取拼⾳/// <summary>/// 获取拼⾳/// </summary>/// <param name="Chstr">汉字字符串</param>/// <param name="IsLoweCase">是否⼩写(默认⼩写即false)</param>/// <param name="Interval">拼⾳之间的间隔(默认⽆间隔)</param>/// <returns>转换后的拼⾳字符串</returns>private string GetPinyin(string Chstr, bool IsLoweCase = false, string Interval = ""){Regex reg = new Regex("^[\u4e00-\u9fa5]$");//验证是否输⼊汉字byte[] arr = new byte[2];string pystr = "";int asc = 0, M1 = 0, M2 = 0;char[] mChar = Chstr.ToCharArray();//获取汉字对应的字符数组for (int j = 0; j < mChar.Length; j++){//如果输⼊的是汉字if (reg.IsMatch(mChar[j].ToString())){arr = System.Text.Encoding.Default.GetBytes(mChar[j].ToString());M1 = (short)(arr[0]);M2 = (short)(arr[1]);asc = M1 * 256 + M2 - 65536;if (asc > 0 && asc < 160){pystr += mChar[j];}else{switch (asc){case -9254:pystr += "Zhen"; break;case -8985:pystr += "Qian"; break;case -5463:pystr += "Jia"; break;case -8274:pystr += "Ge"; break;case -5448:pystr += "Ga"; break;case -5447:pystr += "La"; break;case -4649:pystr += "Chen"; break;case -5436:pystr += "Mao"; break;case -5213:pystr += "Mao"; break;case -3597:pystr += "Die"; break;case -5659:pystr += "Tian"; break;default:for (int i = (getValue.Length - 1); i >= 0; i--){if (getValue[i] <= asc) //判断汉字的拼⾳区编码是否在指定范围内{pystr += getName[i] + Interval;//如果不超出范围则获取对应的拼⾳break;}}break;}}}else //如果不是汉字{pystr += mChar[j].ToString();//如果不是汉字则返回}}if (IsLoweCase)return pystr.ToLower();//返回获取到的汉字拼⾳elsereturn pystr;}#endregion#region 拼⾳区编码数组//定义拼⾳区编码数组private static int[] getValue = new int[]{-20319,-20317,-20304,-20295,-20292,-20283,-20265,-20257,-20242,-20230,-20051,-20036, -20032,-20026,-20002,-19990,-19986,-19982,-19976,-19805,-19784,-19775,-19774,-19763, -19756,-19751,-19746,-19741,-19739,-19728,-19725,-19715,-19540,-19531,-19525,-19515, -19500,-19484,-19479,-19467,-19289,-19288,-19281,-19275,-19270,-19263,-19261,-19249, -19243,-19242,-19238,-19235,-19227,-19224,-19218,-19212,-19038,-19023,-19018,-19006, -19003,-18996,-18977,-18961,-18952,-18783,-18774,-18773,-18763,-18756,-18741,-18735, -18731,-18722,-18710,-18697,-18696,-18526,-18518,-18501,-18490,-18478,-18463,-18448, -18447,-18446,-18239,-18237,-18231,-18220,-18211,-18201,-18184,-18183, -18181,-18012, -17997,-17988,-17970,-17964,-17961,-17950,-17947,-17931,-17928,-17922,-17759,-17752, -17733,-17730,-17721,-17703,-17701,-17697,-17692,-17683,-17676,-17496,-17487,-17482, -17468,-17454,-17433,-17427,-17417,-17202,-17185,-16983,-16970,-16942,-16915,-16733, -16708,-16706,-16689,-16664,-16657,-16647,-16474,-16470,-16465,-16459,-16452,-16448, -16433,-16429,-16427,-16423,-16419,-16412,-16407,-16403,-16401,-16393,-16220,-16216, -16212,-16205,-16202,-16187,-16180,-16171,-16169,-16158,-16155,-15959,-15958,-15944, -15933,-15920,-15915,-15903,-15889,-15878,-15707,-15701,-15681,-15667,-15661,-15659, -15652,-15640,-15631,-15625,-15454,-15448,-15436,-15435,-15419,-15416,-15408,-15394, -15385,-15377,-15375,-15369,-15363,-15362,-15183,-15180,-15165,-15158,-15153,-15150, -15149,-15144,-15143,-15141,-15140,-15139,-15128,-15121,-15119,-15117,-15110,-15109, -14941,-14937,-14933,-14930,-14929,-14928,-14926,-14922,-14921,-14914,-14908,-14902, -14894,-14889,-14882,-14873,-14871,-14857,-14678,-14674,-14670,-14668,-14663,-14654, -14645,-14630,-14594,-14429,-14407,-14399,-14384,-14379,-14368,-14355,-14353,-14345, -14170,-14159,-14151,-14149,-14145,-14140,-14137,-14135,-14125,-14123,-14122,-14112, -14109,-14099,-14097,-14094,-14092,-14090,-14087,-14083,-13917,-13914,-13910,-13907, -13906,-13905,-13896,-13894,-13878,-13870,-13859,-13847,-13831,-13658,-13611,-13601,-13406,-13404,-13400,-13398,-13395,-13391,-13387,-13383,-13367,-13359,-13356,-13343, -13340,-13329,-13326,-13318,-13147,-13138,-13120,-13107,-13096,-13095,-13091,-13076, -13068,-13063,-13060,-12888,-12875,-12871,-12860,-12858,-12852,-12849,-12838,-12831, -12829,-12812,-12802,-12607,-12597,-12594,-12585,-12556,-12359,-12346,-12320,-12300, -12120,-12099,-12089,-12074,-12067,-12058,-12039,-11867,-11861,-11847,-11831,-11798, -11781,-11604,-11589,-11536,-11358,-11340,-11339,-11324,-11303,-11097,-11077,-11067, -11055,-11052,-11045,-11041,-11038,-11024,-11020,-11019,-11018,-11014,-10838,-10832, -10815,-10800,-10790,-10780,-10764,-10587,-10544,-10533,-10519,-10331,-10329,-10328, -10322,-10315,-10309,-10307,-10296,-10281,-10274,-10270,-10262,-10260,-10256,-10254 };//定义拼⾳数组private static string[] getName = new string[]{ "A","Ai","An","Ang","Ao","Ba","Bai","Ban","Bang","Bao","Bei","Ben", "Beng","Bi","Bian","Biao","Bie","Bin","Bing","Bo","Bu","Ba","Cai","Can", "Cang","Cao","Ce","Ceng","Cha","Chai","Chan","Chang","Chao","Che","Chen","Cheng", "Chi","Chong","Chou","Chu","Chuai","Chuan","Chuang","Chui","Chun","Chuo","Ci","Cong", "Cou","Cu","Cuan","Cui","Cun","Cuo","Da","Dai","Dan","Dang","Dao","De", "Deng","Di","Dian","Diao","Die","Ding","Diu","Dong","Dou","Du","Duan","Dui", "Dun","Duo","E","En","Er","Fa","Fan","Fang","Fei","Fen","Feng","Fo", "Fou","Fu","Ga","Gai","Gan","Gang","Gao","Ge","Gei","Gen","Geng","Gong", "Gou","Gu","Gua","Guai","Guan","Guang","Gui","Gun","Guo","Ha","Hai","Han", "Hang","Hao","He","Hei","Hen","Heng","Hong","Hou","Hu","Hua","Huai","Huan", "Huang","Hui","Hun","Huo","Ji","Jia","Jian","Jiang","Jiao","Jie","Jin","Jing", "Jiong","Jiu","Ju","Juan","Jue","Jun","Ka","Kai","Kan","Kang","Kao","Ke", "Ken","Keng","Kong","Kou","Ku","Kua","Kuai","Kuan","Kuang","Kui","Kun","Kuo", "La","Lai","Lan","Lang","Lao","Le","Lei","Leng","Li","Lia","Lian","Liang", "Liao","Lie","Lin","Ling","Liu","Long","Lou","Lu","Lv","Luan","Lue","Lun", "Luo","Ma","Mai","Man","Mang","Mao","Me","Mei","Men","Meng","Mi","Mian", "Miao","Mie","Min","Ming","Miu","Mo","Mou","Mu","Na","Nai","Nan","Nang", "Nao","Ne","Nei","Nen","Neng","Ni","Nian","Niang","Niao","Nie","Nin","Ning", "Niu","Nong","Nu","Nv","Nuan","Nue","Nuo","O","Ou","Pa","Pai","Pan", "Pang","Pao","Pei","Pen","Peng","Pi","Pian","Piao","Pie","Pin","Ping","Po", "Pu","Qi","Qia","Qian","Qiang","Qiao","Qie","Qin","Qing","Qiong","Qiu","Qu", "Quan","Que","Qun","Ran","Rang","Rao","Re","Ren","Reng","Ri","Rong","Rou", "Ru","Ruan","Rui","Run","Ruo","Sa","Sai","San","Sang","Sao","Se","Sen", "Seng","Sha","Shai","Shan","Shang","Shao","She","Shen","Sheng","Shi","Shou","Shu", "Shua","Shuai","Shuan","Shuang","Shui","Shun","Shuo","Si","Song","Sou","Su","Suan", "Sui","Sun","Suo","Ta","Tai","Tan","Tang","Tao","Te","Teng","Ti","Tian", "Tiao","Tie","Ting","Tong","Tou","Tu","Tuan","Tui","Tun","Tuo","Wa","Wai", "Wan","Wang","Wei","Wen","Weng","Wo","Wu","Xi","Xia","Xian","Xiang","Xiao", "Xie","Xin","Xing","Xiong","Xiu","Xu","Xuan","Xue","Xun","Ya","Yan","Yang", "Yao","Ye","Yi","Yin","Ying","Yo","Yong","You","Yu","Yuan","Yue","Yun","Za", "Zai","Zan","Zang","Zao","Ze","Zei","Zen","Zeng","Zha","Zhai","Zhan", "Zhang","Zhao","Zhe","Zhen","Zheng","Zhi","Zhong","Zhou","Zhu","Zhua","Zhuai","Zhuan", "Zhuang","Zhui","Zhun","Zhuo","Zi","Zong","Zou","Zu","Zuan","Zui","Zun","Zuo"};#endregion}。