db上での半角全角変換
半角カナでdbにデータを入れたい、DB上の全角カナを半角カナに変更して出力したい。
ということはよくあることかと思います。
特に金融や医療関係のシステムだと要件に入ってくることも多い。
また、古い外部の決済システムや、販売代行会社などを使っていると、顧客名簿や商品名が半角カナのcsvで送られてくることもあります。
これらのデータを半角↔全角をあまり意識しないようにシステムを組みたい。
ただ、この処理をアプリケーション側で定義すると下記のような問題が置きます。
デメリット
- 金融や医療関係のシステムの場合は複数のベンダーが入ってくることが多く、それぞれ別のアプリケーションを作っている場合が多いため、アプリケーションごとにそれぞれ別の半角カナ変換ロジックを管理することになり大変。
- アプリケーション側で実装すると速度が遅い。
半角カナの処理は業務上、それなりの頻度で実行する処理かバッチ処理に組み込まれることが多いので、早くしておきたいというのもあります。
またプログラムを書くにあたって、合字対策や反転処理をなど、知識というよりシンプルにプログラミングスキルが試される処理になるので、あまり外に投げないほうがいい処理かと思います。
どうするか？
上のデメリットはロジックをストアドに寄せて、それをアプリケーションから参照するようにすると、システム全体で半角カナのロジックは一つ管理するだけで良くなり、簡単になる。
もし今後ハングル文字など他の文字の全角半角サポートが増えても、ここに処理を追加するだけで機能修正は終わりです。
Userテーブルのカラムに使う場合は生成カラムからストアドを呼び出すと、全角カナのデータ一つで半角カナを表現できるため、管理も楽になる。既存のシステムが全部ormだとしても、ormとの相性もそこまで悪くない。
ソースコード
postgresqlの例を下に示す。
他のRDBMSも同じ要領です。
function にimmutableは必ずつけてください。
これがないとDBの変更を起こす副作用があるストアドなのかどうか、
DB側で判別できないため、生成カラムが使えなかったり、その他DBの処理に大きな影響を与えます。
これは他のRDBMSでも同じです。
hankakukana2zenkaku_string -> 半角文字列を全角文字列に変更するストアド
zenkaku2hankakukana_string -> 全角文字列を半角文字列に変更するストアド
CREATE FUNCTION zenkaku2hankakukana (c CHAR)
RETURNS VARCHAR(2) AS $$
DECLARE
ret VARCHAR(2);
BEGIN
CASE c
-- ア行
WHEN 'ア' THEN
ret := 'ｱ';
WHEN 'イ' THEN
ret := 'ｲ';
WHEN 'ウ' THEN
ret := 'ｳ';
WHEN 'エ' THEN
ret := 'ｴ';
WHEN 'オ' THEN
ret := 'ｵ';
-- カ行
WHEN 'カ' THEN
ret := 'ｶ';
WHEN 'キ' THEN
ret := 'ｷ';
WHEN 'ク' THEN
ret := 'ｸ';
WHEN 'ケ' THEN
ret := 'ｹ';
WHEN 'コ' THEN
ret := 'ｺ';
-- サ行
WHEN 'サ' THEN
ret := 'ｻ';
WHEN 'シ' THEN
ret := 'ｼ';
WHEN 'ス' THEN
ret := 'ｽ';
WHEN 'セ' THEN
ret := 'ｾ';
WHEN 'ソ' THEN
ret := 'ｿ';
-- タ行
WHEN 'タ' THEN
ret := 'ﾀ';
WHEN 'チ' THEN
ret := 'ﾁ';
WHEN 'ツ' THEN
ret := 'ﾂ';
WHEN 'テ' THEN
ret := 'ﾃ';
WHEN 'ト' THEN
ret := 'ﾄ';
-- ナ行
WHEN 'ナ' THEN
ret := 'ﾅ';
WHEN 'ニ' THEN
ret := 'ﾆ';
WHEN 'ヌ' THEN
ret := 'ﾇ';
WHEN 'ネ' THEN
ret := 'ﾈ';
WHEN 'ノ' THEN
ret := 'ﾉ';
-- ハ行
WHEN 'ハ' THEN
ret := 'ﾊ';
WHEN 'ヒ' THEN
ret := 'ﾋ';
WHEN 'フ' THEN
ret := 'ﾌ';
WHEN 'ヘ' THEN
ret := 'ﾍ';
WHEN 'ホ' THEN
ret := 'ﾎ';
-- マ行
WHEN 'マ' THEN
ret := 'ﾏ';
WHEN 'ミ' THEN
ret := 'ﾐ';
WHEN 'ム' THEN
ret := 'ﾑ';
WHEN 'メ' THEN
ret := 'ﾒ';
WHEN 'モ' THEN
ret := 'ﾓ';
-- ヤ行
WHEN 'ヤ' THEN
ret := 'ﾔ';
WHEN 'ユ' THEN
ret := 'ﾕ';
WHEN 'ヨ' THEN
ret := 'ﾖ';
-- ワ行
WHEN 'ワ' THEN
ret := 'ﾜ';
WHEN 'ヲ' THEN
ret := 'ｦ';
WHEN 'ン' THEN
ret := 'ﾝ';
-- ガ行
WHEN 'ガ' THEN
ret := 'ｶﾞ';
WHEN 'ギ' THEN
ret := 'ｷﾞ';
WHEN 'グ' THEN
ret := 'ｸﾞ';
WHEN 'ゲ' THEN
ret := 'ｹﾞ';
WHEN 'ゴ' THEN
ret := 'ｺﾞ';
-- ザ行
WHEN 'ザ' THEN
ret := 'ｻﾞ';
WHEN 'ジ' THEN
ret := 'ｼﾞ';
WHEN 'ズ' THEN
ret := 'ｽﾞ';
WHEN 'ゼ' THEN
ret := 'ｾﾞ';
WHEN 'ゾ' THEN
ret := 'ｿﾞ';
-- ダ行
WHEN 'ダ' THEN
ret := 'ﾀﾞ';
WHEN 'ヂ' THEN
ret := 'ﾁﾞ';
WHEN 'ヅ' THEN
ret := 'ﾂﾞ';
WHEN 'デ' THEN
ret := 'ﾃﾞ';
WHEN 'ド' THEN
ret := 'ﾄﾞ';
-- バ行
WHEN 'バ' THEN
ret := 'ﾊﾞ';
WHEN 'ビ' THEN
ret := 'ﾋﾞ';
WHEN 'ブ' THEN
ret := 'ﾌﾞ';
WHEN 'ベ' THEN
ret := 'ﾍﾞ';
WHEN 'ボ' THEN
ret := 'ﾎﾞ';
-- パ行
WHEN 'パ' THEN
ret := 'ﾊﾟ';
WHEN 'ピ' THEN
ret := 'ﾋﾟ';
WHEN 'プ' THEN
ret := 'ﾌﾟ';
WHEN 'ペ' THEN
ret := 'ﾍﾟ';
WHEN 'ポ' THEN
ret := 'ﾎﾟ';
-- 小文字
WHEN 'ァ' THEN
ret := 'ｧ';
WHEN 'ィ' THEN
ret := 'ｨ';
WHEN 'ゥ' THEN
ret := 'ｩ';
WHEN 'ェ' THEN
ret := 'ｪ';
WHEN 'ォ' THEN
ret := 'ｫ';
WHEN 'ッ' THEN
ret := 'ｯ';
WHEN 'ャ' THEN
ret := 'ｬ';
WHEN 'ュ' THEN
ret := 'ｭ';
WHEN 'ョ' THEN
ret := 'ｮ';
-- その他 伸ばし棒ー（マイナスとは違う）
WHEN 'ー' THEN
ret := 'ｰ';
-- アルファベット
-- 小文字
WHEN 'ａ' THEN
ret := 'a';
WHEN 'ｂ' THEN
ret := 'b';
WHEN 'ｃ' THEN
ret := 'c';
WHEN 'ｄ' THEN
ret := 'd';
WHEN 'ｅ' THEN
ret := 'e';
WHEN 'ｆ' THEN
ret := 'f';
WHEN 'ｇ' THEN
ret := 'g';
WHEN 'ｈ' THEN
ret := 'h';
WHEN 'ｉ' THEN
ret := 'i';
WHEN 'ｊ' THEN
ret := 'j';
WHEN 'ｋ' THEN
ret := 'k';
WHEN 'ｌ' THEN
ret := 'l';
WHEN 'ｍ' THEN
ret := 'm';
WHEN 'ｎ' THEN
ret := 'n';
WHEN 'ｏ' THEN
ret := 'o';
WHEN 'ｐ' THEN
ret := 'p';
WHEN 'ｑ' THEN
ret := 'q';
WHEN 'ｒ' THEN
ret := 'r';
WHEN 'ｓ' THEN
ret := 's';
WHEN 'ｔ' THEN
ret := 't';
WHEN 'ｕ' THEN
ret := 'u';
WHEN 'ｖ' THEN
ret := 'v';
WHEN 'ｗ' THEN
ret := 'w';
WHEN 'ｘ' THEN
ret := 'x';
WHEN 'ｙ' THEN
ret := 'y';
WHEN 'ｚ' THEN
ret := 'z';
-- 大文字
WHEN 'Ａ' THEN
ret := 'A';
WHEN 'Ｂ' THEN
ret := 'B';
WHEN 'Ｃ' THEN
ret := 'C';
WHEN 'Ｄ' THEN
ret := 'D';
WHEN 'Ｅ' THEN
ret := 'E';
WHEN 'Ｆ' THEN
ret := 'F';
WHEN 'Ｇ' THEN
ret := 'G';
WHEN 'Ｈ' THEN
ret := 'H';
WHEN 'Ｉ' THEN
ret := 'I';
WHEN 'Ｊ' THEN
ret := 'J';
WHEN 'Ｋ' THEN
ret := 'K';
WHEN 'Ｌ' THEN
ret := 'L';
WHEN 'Ｍ' THEN
ret := 'M';
WHEN 'Ｎ' THEN
ret := 'N';
WHEN 'Ｏ' THEN
ret := 'O';
WHEN 'Ｐ' THEN
ret := 'P';
WHEN 'Ｑ' THEN
ret := 'Q';
WHEN 'Ｒ' THEN
ret := 'R';
WHEN 'Ｓ' THEN
ret := 'S';
WHEN 'Ｔ' THEN
ret := 'T';
WHEN 'Ｕ' THEN
ret := 'U';
WHEN 'Ｖ' THEN
ret := 'V';
WHEN 'Ｗ' THEN
ret := 'W';
WHEN 'Ｘ' THEN
ret := 'X';
WHEN 'Ｙ' THEN
ret := 'Y';
WHEN 'Ｚ' THEN
ret := 'Z';
-- 数字
WHEN '０' THEN
ret := '0';
WHEN '１' THEN
ret := '1';
WHEN '２' THEN
ret := '2';
WHEN '３' THEN
ret := '3';
WHEN '４' THEN
ret := '4';
WHEN '５' THEN
ret := '5';
WHEN '６' THEN
ret := '6';
WHEN '７' THEN
ret := '7';
WHEN '８' THEN
ret := '8';
WHEN '９' THEN
ret := '9';
ELSE
-- 定義されていないものはそのまま返す
ret := c;
END CASE;
RETURN ret;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- 全角文字列を半角文字列に変更する関数。
CREATE FUNCTION zenkaku2hankakukana_string (str TEXT)
RETURNS TEXT AS $$
DECLARE
ret TEXT;
i INTEGER := 1;
-- 何文字取るか
cap INTEGER := 1;
BEGIN
WHILE i <= char_length(str)
LOOP
ret := concat(ret, zenkaku2hankakukana(SUBSTRING(str, i, cap)));
-- 取った数だけ進める
i := i + cap;
END LOOP;
RETURN ret;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- 半角を全角に変更する
CREATE FUNCTION hankakukana2zenkaku (c VARCHAR(2))
RETURNS CHAR(1) AS $$
DECLARE
ret CHAR(1);
BEGIN
CASE c
-- ア行
WHEN 'ｱ' THEN
ret := 'ア';
WHEN 'ｲ' THEN
ret := 'イ';
WHEN 'ｳ' THEN
ret := 'ウ';
WHEN 'ｴ' THEN
ret := 'エ';
WHEN 'ｵ' THEN
ret := 'オ';
-- カ行
WHEN 'ｶ' THEN
ret := 'カ';
WHEN 'ｷ' THEN
ret := 'キ';
WHEN 'ｸ' THEN
ret := 'ク';
WHEN 'ｹ' THEN
ret := 'ケ';
WHEN 'ｺ' THEN
ret := 'コ';
-- サ行
WHEN 'ｻ' THEN
ret := 'サ';
WHEN 'ｼ' THEN
ret := 'シ';
WHEN 'ｽ' THEN
ret := 'ス';
WHEN 'ｾ' THEN
ret := 'セ';
WHEN 'ｿ' THEN
ret := 'ソ';
-- タ行
WHEN 'ﾀ' THEN
ret := 'タ';
WHEN 'ﾁ' THEN
ret := 'チ';
WHEN 'ﾂ' THEN
ret := 'ツ';
WHEN 'ﾃ' THEN
ret := 'ﾃ';
WHEN 'ﾄ' THEN
ret := 'ト';
-- ナ行
WHEN 'ﾅ' THEN
ret := 'ナ';
WHEN 'ﾆ' THEN
ret := 'ニ';
WHEN 'ﾇ' THEN
ret := 'ヌ';
WHEN 'ﾈ' THEN
ret := 'ネ';
WHEN 'ﾉ' THEN
ret := 'ノ';
-- ハ行
WHEN 'ﾊ' THEN
ret := 'ハ';
WHEN 'ﾋ' THEN
ret := 'ヒ';
WHEN 'ﾌ' THEN
ret := 'フ';
WHEN 'ﾍ' THEN
ret := 'ヘ';
WHEN 'ﾎ' THEN
ret := 'ホ';
-- マ行
WHEN 'ﾏ' THEN
ret := 'マ';
WHEN 'ﾐ' THEN
ret := 'ミ';
WHEN 'ﾑ' THEN
ret := 'ム';
WHEN 'ﾒ' THEN
ret := 'メ';
WHEN 'ﾓ' THEN
ret := 'モ';
-- ヤ行
WHEN 'ﾔ' THEN
ret := 'ヤ';
WHEN 'ﾕ' THEN
ret := 'ユ';
WHEN 'ﾖ' THEN
ret := 'ヨ';
-- ワ行
WHEN 'ﾜ' THEN
ret := 'ワ';
WHEN 'ｦ' THEN
ret := 'ヲ';
WHEN 'ﾝ' THEN
ret := 'ン';
-- ガ行
WHEN 'ｶﾞ' THEN
ret := 'ガ';
WHEN 'ｷﾞ' THEN
ret := 'ギ';
WHEN 'ｸﾞ' THEN
ret := 'グ';
WHEN 'ｹﾞ' THEN
ret := 'ゲ';
WHEN 'ｺﾞ' THEN
ret := 'ゴ';
-- ザ行
WHEN 'ｻﾞ' THEN
ret := 'ザ';
WHEN 'ｼﾞ' THEN
ret := 'ジ';
WHEN 'ｽﾞ' THEN
ret := 'ズ';
WHEN 'ｾﾞ' THEN
ret := 'ゼ';
WHEN 'ｿﾞ' THEN
ret := 'ゾ';
-- ダ行
WHEN 'ﾀﾞ' THEN
ret := 'ダ';
WHEN 'ﾁﾞ' THEN
ret := 'ヂ';
WHEN 'ﾂﾞ' THEN
ret := 'ヅ';
WHEN 'ﾃﾞ' THEN
ret := 'デ';
WHEN 'ﾄﾞ' THEN
ret := 'ド';
-- バ行
WHEN 'ﾊﾞ' THEN
ret := 'バ';
WHEN 'ﾋﾞ' THEN
ret := 'ビ';
WHEN 'ﾌﾞ' THEN
ret := 'ブ';
WHEN 'ﾍﾞ' THEN
ret := 'ベ';
WHEN 'ﾎﾞ' THEN
ret := 'ボ';
-- パ行
WHEN 'ﾊﾟ' THEN
ret := 'パ';
WHEN 'ﾋﾟ' THEN
ret := 'ピ';
WHEN 'ﾌﾟ' THEN
ret := 'プ';
WHEN 'ﾍﾟ' THEN
ret := 'ペ';
WHEN 'ﾎﾟ' THEN
ret := 'ポ';
-- 小文字
WHEN 'ｧ' THEN
ret := 'ァ';
WHEN 'ｨ' THEN
ret := 'ィ';
WHEN 'ｩ' THEN
ret := 'ゥ';
WHEN 'ｪ' THEN
ret := 'ェ';
WHEN 'ｫ' THEN
ret := 'ォ';
WHEN 'ｯ' THEN
ret := 'ッ';
WHEN 'ｬ' THEN
ret := 'ャ';
WHEN 'ｭ' THEN
ret := 'ュ';
WHEN 'ｮ' THEN
ret := 'ョ';
-- その他 伸ばし棒-(マイナス)とは違う
WHEN 'ｰ' THEN
ret := 'ー';
-- アルファベット
-- 小文字
WHEN 'a' THEN
ret := 'ａ';
WHEN 'b' THEN
ret := 'ｂ';
WHEN 'c' THEN
ret := 'ｃ';
WHEN 'd' THEN
ret := 'ｄ';
WHEN 'e' THEN
ret := 'ｅ';
WHEN 'f' THEN
ret := 'ｆ';
WHEN 'g' THEN
ret := 'ｇ';
WHEN 'h' THEN
ret := 'ｈ';
WHEN 'i' THEN
ret := 'ｉ';
WHEN 'j' THEN
ret := 'ｊ';
WHEN 'k' THEN
ret := 'ｋ';
WHEN 'l' THEN
ret := 'ｌ';
WHEN 'm' THEN
ret := 'ｍ';
WHEN 'n' THEN
ret := 'ｎ';
WHEN 'o' THEN
ret := 'ｏ';
WHEN 'p' THEN
ret := 'ｐ';
WHEN 'q' THEN
ret := 'ｑ';
WHEN 'r' THEN
ret := 'ｒ';
WHEN 's' THEN
ret := 'ｓ';
WHEN 't' THEN
ret := 'ｔ';
WHEN 'u' THEN
ret := 'ｕ';
WHEN 'v' THEN
ret := 'ｖ';
WHEN 'w' THEN
ret := 'ｗ';
WHEN 'x' THEN
ret := 'ｘ';
WHEN 'y' THEN
ret := 'ｙ';
WHEN 'z' THEN
ret := 'ｚ';
-- 大文字
WHEN 'A' THEN
ret := 'Ａ';
WHEN 'B' THEN
ret := 'Ｂ';
WHEN 'C' THEN
ret := 'Ｃ';
WHEN 'D' THEN
ret := 'Ｄ';
WHEN 'E' THEN
ret := 'Ｅ';
WHEN 'F' THEN
ret := 'Ｆ';
WHEN 'G' THEN
ret := 'Ｇ';
WHEN 'H' THEN
ret := 'Ｈ';
WHEN 'I' THEN
ret := 'Ｉ';
WHEN 'J' THEN
ret := 'Ｊ';
WHEN 'K' THEN
ret := 'Ｋ';
WHEN 'L' THEN
ret := 'Ｌ';
WHEN 'M' THEN
ret := 'Ｍ';
WHEN 'N' THEN
ret := 'Ｎ';
WHEN 'O' THEN
ret := 'Ｏ';
WHEN 'P' THEN
ret := 'Ｐ';
WHEN 'Q' THEN
ret := 'Ｑ';
WHEN 'R' THEN
ret := 'Ｒ';
WHEN 'S' THEN
ret := 'Ｓ';
WHEN 'T' THEN
ret := 'Ｔ';
WHEN 'U' THEN
ret := 'Ｕ';
WHEN 'V' THEN
ret := 'Ｖ';
WHEN 'W' THEN
ret := 'Ｗ';
WHEN 'X' THEN
ret := 'Ｘ';
WHEN 'Y' THEN
ret := 'Ｙ';
WHEN 'Z' THEN
ret := 'Ｚ';
-- 数字
WHEN '0' THEN
ret := '０';
WHEN '1' THEN
ret := '１';
WHEN '2' THEN
ret := '２';
WHEN '3' THEN
ret := '３';
WHEN '4' THEN
ret := '４';
WHEN '5' THEN
ret := '５';
WHEN '6' THEN
ret := '６';
WHEN '7' THEN
ret := '７';
WHEN '8' THEN
ret := '８';
WHEN '9' THEN
ret := '９';
ELSE
-- 定義されていないものはそのまま返す
ret := c;
END CASE;
RETURN ret;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- 合字（二つで一つの文字か判定）
CREATE FUNCTION ligature (c VARCHAR(2))
RETURNS BOOLEAN AS $$
DECLARE
ret BOOLEAN := FALSE;
BEGIN
-- 2文字の時のみ
IF char_length(c) = 2 THEN
-- 濁音、 半濁音は他の音があって初めてなので
-- 実際あるか同時は他の関数に委ねる。漫画などにある表現として「あ」の濁点とかがあるので。
IF Substring(c, 2, 1) IN ('ﾞ', 'ﾟ') THEN
ret := TRUE;
END IF;
-- 2文字以上の合字が出たら足して行く。
-- 3文字以上の合字が出たらELSE IF で追加して行く。
END IF;
RETURN ret;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- 半角文字列を全角文字列に変更する関数。
CREATE FUNCTION hankakukana2zenkaku_string (str TEXT)
RETURNS TEXT AS $$
DECLARE
ret TEXT;
i INTEGER := 1;
-- 何文字取るか
cap INTEGER := 1;
BEGIN
WHILE i <= char_length(str)
LOOP
-- 合字かどうか。
-- 指定の位置から最大二文字取る。
IF ligature(SUBSTRING(str, i, 2)) THEN
-- 2文字で一つの文字なので二文字取る。
cap := 2;
ELSE
cap := 1;
END IF;
ret := concat(ret, hankakukana2zenkaku(SUBSTRING(str, i, cap)));
-- 取った数だけ進める
i := i + cap;
END LOOP;
RETURN ret;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
使い方
first_name, secound_nameに全角文字列が入っていると、
半角文字列がちゃんと出力されます。
SELECT
secound_name
,zenkaku2hankakukana_string(secound_name) AS secound_name_kana
,first_name
,zenkaku2hankakukana_string(first_name) AS first_name_kana
FROM users
下のように生成カラムに組み込むこともできます。
CREATE TABLE users (
id SERIAL NOT NULL,
secound_name varchar(20),
secound_name_kana varchar(20) GENERATED ALWAYS AS (zenkaku2hankakukana_string(secound_name)) STORED
first_name varchar(20),
first_name_kana varchar(20) GENERATED ALWAYS AS (zenkaku2hankakukana_string(first_name)) STORED
);
最新のソースコード
githubにあります。
github
Author And Source
この問題について(db上での半角全角変換), 我々は、より多くの情報をここで見つけました https://zenn.dev/oto/articles/a0ff2597803b44著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Collection and Share based on the CC protocol