有時(shí)我們會(huì)需要將漢字轉(zhuǎn)為拼音，例如需要將省市轉(zhuǎn)為拼音后當(dāng)做編碼存儲(chǔ)（盡管國家有統(tǒng)一的標(biāo)識(shí)碼，但有時(shí)候我們還是會(huì)用到），網(wǎng)絡(luò)上也有工具提供漢字轉(zhuǎn)拼音的功能，但各有優(yōu)劣，一般轉(zhuǎn)拼音后還會(huì)存在帶聲調(diào)的字母，可以用sql統(tǒng)一轉(zhuǎn)換。

DROP TABLE PINYIN;CREATE TABLE PINYIN(ID VARCHAR2(200),B VARCHAR2(200),C VARCHAR2(200),D VARCHAR2(200),E VARCHAR2(200));--用金山詞霸漢字轉(zhuǎn)拼音工具先轉(zhuǎn)有聲調(diào)的拼音，拷貝到E列中 --將excel中的數(shù)據(jù)首列加上ID，然后拷貝到該表中，B：省份，C：城市，D：區(qū)縣，E：拼音 SELECT * FROM PINYIN FOR UPDATE;--將聲調(diào)替換為字母,因?yàn)閹曊{(diào)的都是元音字母，因此只需要替換5*4個(gè)聲調(diào)即可 update pinyin t set t.e=REPLACE (T.e,'ā','a'); update pinyin t set t.e=REPLACE (T.e,'á','a'); update pinyin t set t.e=REPLACE (T.e,'ǎ','a'); update pinyin t set t.e=REPLACE (T.e,'à','a'); update pinyin t set t.e=REPLACE (T.e,'ō','o'); update pinyin t set t.e=REPLACE (T.e,'ó','o'); update pinyin t set t.e=REPLACE (T.e,'ǒ','o'); update pinyin t set t.e=REPLACE (T.e,'ò','o'); update pinyin t set t.e=REPLACE (T.e,'ê','e'); update pinyin t set t.e=REPLACE (T.e,'ē','e'); update pinyin t set t.e=REPLACE (T.e,'é','e'); update pinyin t set t.e=REPLACE (T.e,'ě','e'); update pinyin t set t.e=REPLACE (T.e,'è','e'); update pinyin t set t.e=REPLACE (T.e,'ī','i'); update pinyin t set t.e=REPLACE (T.e,'í','i'); update pinyin t set t.e=REPLACE (T.e,'ǐ','i'); update pinyin t set t.e=REPLACE (T.e,'ì','i'); update pinyin t set t.e=REPLACE (T.e,'ū','u'); update pinyin t set t.e=REPLACE (T.e,'ú','u'); update pinyin t set t.e=REPLACE (T.e,'ǔ','u'); update pinyin t set t.e=REPLACE (T.e,'ù','u'); update pinyin t set t.e=REPLACE (T.e,'ǖ','v'); update pinyin t set t.e=REPLACE (T.e,'ǘ','v'); update pinyin t set t.e=REPLACE (T.e,'ǚ','v'); update pinyin t set t.e=REPLACE (T.e,'ǜ','v'); update pinyin t set t.e=REPLACE (T.e,'ü','v');--去首尾空格，并且首字母大寫 update pinyin set e=trim(e); update pinyin set e=initcap(e);--查詢有無重復(fù)的拼音，給重復(fù)的拼音后面加2,3,4,5 select *from pinyin twhere t.e in (select e from pinyin group by e having count(1) > 1)order by t.e;--和上一步循環(huán)執(zhí)行，直到?jīng)]有重復(fù)項(xiàng)update pinyin set e=e||'2'where pinyin.id in (select max(id)from (select t.id, t.b, t.c, t.d, t.efrom pinyin twhere t.e in (select e from pinyin group by e having count(1) > 1)order by t.e) sgroup by s.e);commit;select * from pinyin order by to_number(id) ;

?

轉(zhuǎn)載于:https://www.cnblogs.com/eva-wu/p/3459728.html

總結(jié)

以上是生活随笔為你收集整理的用sql实现汉字转拼音的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。