半角カナを全角カナ

postgresql

週記くらい | plpgsql 全角カナ 半角カナ 比較

 CREATE FUNCTION han2zen(varchar) RETURNS varchar AS
 '
 DECLARE
   p1  alias for $1;
   str varchar;
   len int;
   i int;
   zenkakus varchar;
   hankakus varchar;
 BEGIN
   str := p1;
   str := replace(str, ''ｶﾞ'', ''ガ'');
   str := replace(str, ''ｷﾞ'', ''ギ'');
   str := replace(str, ''ｸﾞ'', ''グ'');
   str := replace(str, ''ｹﾞ'', ''ゲ'');
   str := replace(str, ''ｺﾞ'', ''ゴ'');
   str := replace(str, ''ｻﾞ'', ''ザ'');
   str := replace(str, ''ｼﾞ'', ''ジ'');
   str := replace(str, ''ｽﾞ'', ''ズ'');
   str := replace(str, ''ｾﾞ'', ''ゼ'');
   str := replace(str, ''ｿﾞ'', ''ゾ'');
   str := replace(str, ''ﾀﾞ'', ''ダ'');
   str := replace(str, ''ﾁﾞ'', ''ヂ'');
   str := replace(str, ''ﾂﾞ'', ''ヅ'');
   str := replace(str, ''ﾃﾞ'', ''デ'');
   str := replace(str, ''ﾄﾞ'', ''ド'');
   str := replace(str, ''ﾊﾞ'', ''バ'');
   str := replace(str, ''ﾋﾞ'', ''ビ'');
   str := replace(str, ''ﾌﾞ'', ''ブ'');
   str := replace(str, ''ﾍﾞ'', ''ベ'');
   str := replace(str, ''ﾎﾞ'', ''ボ'');
   str := replace(str, ''ﾊﾟ'', ''パ'');
   str := replace(str, ''ﾋﾟ'', ''ピ'');
   str := replace(str, ''ﾌﾟ'', ''プ'');
   str := replace(str, ''ﾍﾟ'', ''ペ'');
   str := replace(str, ''ﾎﾟ'', ''ポ'');
   str := replace(str, ''ｳﾞ'', ''ヴ'');
   zenkakus := ''アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォッャュョー・'';
   hankakus := ''ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｯｬｭｮｰ･'';
   len := length(zenkakus);
   for i in 0 .. len loop
     str := replace(str, substr(hankakus, i, 1), substr(zenkakus, i, 1));
   end loop;
   return str;
   END;
 '
 LANGUAGE 'plpgsql'
 ;

UPDATE table_name SET col_name=kana2full(col_name);

または、CREATE FUNCTION を han2zen.sql に保存して

\i /path/to/han2zen.sql
UPDATE table_name SET col_name=kana2full(col_name);


登録した関数の削除

DROP FUNCTION han2zen(varchar);