OracleのTRANSLATE関数についてメモ
イメージ的には、replace関数のもっと使い勝手がいい版というか、
replaceでやると面倒くさくなる手間を省くためのショートカット関数みたいな、
そんな感じである。
簡単にいうと「一括置換」といった感じなんだろうか。
使い方としては
translate([(A)対象項目] , [(B)置換対象文字] , [(C)置換先対象文字])
という感じである。
[(B)置換対象文字]と[(C)置換先対象文字]は通常同一桁数である必要がある。
[(B)置換対象文字]の１文字目を[(C)置換先対象文字]の１文字目に、
[(B)置換対象文字]の２文字目を[(C)置換先対象文字]の２文字目に、
…という風に対応する位置にある文字に置換する関数だからである。
つまり
select
translate('あいうえお' , 'あい' , 'アイ')
from dual
とかくと、
'あ'を'ア'に、'い'を'イ'に変換する。
（よってこの例だと返って来る値は「アイうえお」になる）
[(B)置換対象文字]と[(C)置換先対象文字]の指定文字数が異なる場合、
特に(B)の文字数より(C)の文字数が少ない（(B)の指定に対して対応する置換先文字が見つからない）場合、
対応できない位置に指定された文字はnull（空文字）にされる。
例えば
select
translate('あいうえお' , 'あいう' , 'アイ')
from dual
だと、返って来る値は「アイえお」になる（「う」がnullになった）。
逆に、
(B)の文字数より(C)の文字数が多い（(B)の指定に対してそれ以上の置換先文字が指定されている）場合、
(C)側で余分に指定された文字が置換されずに単にそのまま値が返却される。
例えば
select
translate('あいうえお' , 'あい' , 'アイウ')
from dual
だと、返って来る値は「アイうえお」になる（「ウ」が置換されなかった）。
ちなみにどっちかというと個人的な使い方は「置換」とは別のところにあり、
”元項目の文字仕様に対して、通常発生し得ない文字に意図的に置換することで、不正文字が発生したデータを探し出す”
ような用途で使っている。
例えば「全角項目に半角数字が発生した」という場合、
（１）全角項目の中の半角数値を一度ダミーの文字（例えば「@」とか）にtranslateで一括置換する
（２）（１）の結果に対して「@」で部分一致したら元項目に半角数値があったと見做す
という手順を踏めば簡易的に不正データ（この場合、全角項目のくせして中に半角数字を含むデータ）検知が可能となる。
例えば以下のようなテーブルTEST_TAB2があったとする。
NoTEST_STR
| 1 | (null) |
| 2 | あいうえお |
| 3 | アイウエオ |
| 4 | ＡＢＣＤＥ |
| 5 | あい1うえお |
| 6 | ＡＢ9ＣＤＥ |
このテーブルの「TEST_STR」が「全角文字しか入らない項目」という前提の仕様だった場合、
この「TEST_STR」という項目に半角が入っているのは異常と判断できる。
↑の例で言うとNo.5とNo.6に半角数字の「1」と「9」が混入しているので、この２レコードは不正データとなる。
この２レコードを検知する場合、
select
no
,test_str
,translate(test_str , '1234567890' ,'@@@@@@@@@@') translate_test_str
from TEST_TAB2 a
where translate(test_str , '1234567890' ,'@@@@@@@@@@') like '%@%'
とすると、No.5とNo.6だけがひっかかってくる。
TEST_STRの中にある半角数字群「1234567890」を一度ダミー文字「@」に置換したうえで
置換結果文字が「@」と部分一致しているとＮＧ、と見做すＳＱＬ。
TEST_STRは全角項目のはずだから、通常半角数字は発生し得ない。
よって「1234567890」を置換しても、そもそも元項目にそんな文字が入っていない前提なのだから
置換は発生しないはずである＝置換後の文字に「@」が入るはずがないのである。
逆に言えば置換後の文字に「@」がいるなら「@」に置換される対象文字が元項目に入っていたというわけだから、
そのデータは不正データであると見做すことができる、
というのがこのやり方である。
当然だが元項目に（置換前の項目に）「@」が入る仕様だとすると
このやり方は成り立たない（置換前から存在し得る文字に置換してもチェックにならない）ので
項目の文字仕様をあらかじめ正確に把握しておく必要があるし、
不正データの対象が「半角数字」だけだからこの程度の簡単なtranslateで済んでいるが、
これが「半角文字全部」となるとtranslateが膨らんでいきその分ＳＱＬも遅くなる。
(まあ半角文字程度だったら大した量にもならんけど)
そういう点を考慮すれば”あくまで暫定的・簡易的なチェックである”という前提を頭の片隅に置いて使うべきであり、
いずれにせよＳＱＬ実行後の目視チェックは必要不可欠であろう。
ただまあ闇雲に不正データチェックするよりは格段に効率がいいやり方として個人的によく使っている。
