SQLでカラム内の「英語表記」を「日本語表記」に一括変換したい（現状はREPLACEを使用）

Question

SQLで、カラム内の「英語表記」を「日本語表記」に一括変換したいと思います。
例）
English1→日本語1
English2→日本語2

REPLACE( Var, 'English1', '日本語1')

現状、以下のようなネスト構造で記述しています。

REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( Var, 'English1', '日本語1'), 'English2', '日本語2'), 'English3', '日本語3'), 'English4', '日本語4'), 'English5', '日本語5'), 'English6', '日本語6'), 'English7', '日本語7'), 'English8', '日本語8'), 'English9', '日本語9'), 'English10', '日本語10')

しかし、

English100→日本語100

のように追加・変更等が発生した場合、アナログで記述対応となってしまいます。

一般的に、上記のような場合、どのような処理をするものでしょうか？

今回の場合、ページ内の「検索パラメーター」部分を「English*」として抽出し、
上記の「REPLACE」を「ネスト化」させることで「日本語」に一括変換しております。

よろしくお願いします。

【追記】
今回、ネストされた（ARRAY:配列内の）文字を置換したいのですが、
単純にUNNESTした場合、レコードの重複が発生してしまいます。

フローとして、

1.SPLITでページパス内の「検索パラメーター部分」（複数ある場合も有り）を、
1レコード内にネスト化して格納。
2.その「検索パラメーター部分」を置換処理「英語」→「日本語」
3.さらに「日本語」で並び替え処理
4.ネスト内のすべてをARRAY_TO_STRINGで「,」区切りとして1レコードに格納
→結果、ネスト構造は解消（無くなる）

＊上記で、「ネスト内の置換・並び替え処理」ができる方法は御座いますでしょうか？

すみませんが、よろしくお願いします。

Answer 1

Oracle,SQLServer,PostgreSQL,MariaDBならば正規表現をつかった置き換えが可能なようです。

REGEXP_REPLACE(var,"English(\d.*)","日本語\1")

MySQLだと、mysql-udf-regexpのようなユーザ定義関数で実現できそうです。
製品によって多少の方言はあるかもしれません。

Comments

[SatoshiMaruyama]

こちらもありがとうございます。BigQueryもユーザ定義関数(UDF)がありますので、チャレンジしたかったのですが、JS記述の為、少々、スキル的に時間的に難しかった次第です。ありがとうございます。

Answer 2

１文字の変換ならTRANSLATEが効率的ですが、単語を変換するなら、REPLACE系しか無いですね。

自動化ということなら、変換パターンをテーブル化しておき、そのテーブルからＳＱＬ文を生成するストアドとすることでしょうか。

Comments

[SatoshiMaruyama]

ありがとうございました。変換パターンをBigQueryにテーブル化して、そこに「インデックス」を持たせて、再度、呼び戻す（日本語に変換する）という対応をしました。「ストアド」について調べましたが、少々、難しそうですね。。次回、チャレンジしてみたいと思います。

Answer 3

一般的に、上記のような場合、どのような処理をするものでしょうか？

そもそも、翻訳をSQLで行うこと自体が一般的ではないと思いますが・・・
CASE式で対応できないでしょうか。

Comments

[momon-ga]

おっと、カラムの一部を変換したいのですよね。CASEは無理でした。

[SatoshiMaruyama]

回答ありがとうございました。CASE文では、その後の追加等、変更対応時にメンテナンスが煩雑になり、直接、SQLで記述する範囲を最小限に抑えたかった次第です。参考になりました。ありがとうございます。