用sql实现汉字转拼音

有时我们会需要将汉字转为拼音，例如需要将省市转为拼音后当做编码存储（尽管国家有统一的标识码，但有时候我们还是会用到），网络上也有工具提供汉字转拼音的功能，但各有优劣，一般转拼音后还会存在带声调的字母，可以用sql统一转换。

DROP TABLE PINYIN;

CREATE TABLE PINYIN(ID VARCHAR2(200),B VARCHAR2(200),C VARCHAR2(200),D VARCHAR2(200),E VARCHAR2(200));

--用金山词霸汉字转拼音工具先转有声调的拼音，拷贝到E列中
--将excel中的数据首列加上ID，然后拷贝到该表中，B：省份，C：城市，D：区县，E：拼音
SELECT * FROM PINYIN FOR UPDATE;

--将声调替换为字母,因为带声调的都是元音字母，因此只需要替换5*4个声调即可
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);

--查询有无重复的拼音，给重复的拼音后面加2,3,4,5
select *
  from pinyin t
 where t.e in (select e from pinyin group by e having count(1) > 1)
 order by t.e;

 --和上一步循环执行，直到没有重复项
 update pinyin set e=e||'2'
 where pinyin.id in (
 select max(id)
   from (select t.id, t.b, t.c, t.d, t.e
           from pinyin t
          where t.e in (select e from pinyin group by e having count(1) > 1)
          order by t.e) s
  group by s.e);
 commit;
  
select * from pinyin order by to_number(id) ;