db上での半角全角変換

半角カナでdbにデータを入れたい、DB上の全角カナを半角カナに変更して出力したい。
ということはよくあることかと思います。
特に金融や医療関係のシステムだと要件に入ってくることも多い。
また、古い外部の決済システムや、販売代行会社などを使っていると、顧客名簿や商品名が半角カナのcsvで送られてくることもあります。
これらのデータを半角↔全角をあまり意識しないようにシステムを組みたい。

ただ、この処理をアプリケーション側で定義すると下記のような問題が置きます。

デメリット

1. 金融や医療関係のシステムの場合は複数のベンダーが入ってくることが多く、それぞれ別のアプリケーションを作っている場合が多いため、アプリケーションごとにそれぞれ別の半角カナ変換ロジックを管理することになり大変。
2. アプリケーション側で実装すると速度が遅い。

半角カナの処理は業務上、それなりの頻度で実行する処理かバッチ処理に組み込まれることが多いので、早くしておきたいというのもあります。
またプログラムを書くにあたって、合字対策や反転処理をなど、知識というよりシンプルにプログラミングスキルが試される処理になるので、あまり外に投げないほうがいい処理かと思います。

どうするか？

上のデメリットはロジックをストアドに寄せて、それをアプリケーションから参照するようにすると、システム全体で半角カナのロジックは一つ管理するだけで良くなり、簡単になる。
もし今後ハングル文字など他の文字の全角半角サポートが増えても、ここに処理を追加するだけで機能修正は終わりです。
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