Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

文字列を文字単位に置換する、改行コード（任意の文字）を削除する

TRANSLATE ( string , search , translate )
return string

string	置換する対象の文字列全体
search	置換したい検索文字列
translate	置換後の文字列

• 置換した文字列

• SQL および PL/SQL の両方で使用可能。

TRANSLATE 関数の内容

文字列 string のなかに文字列 search の１つでも含まれる文字がある場合、文字列 translate における先頭からの同じ位置(※) にある１文字に置換する。 文字列 translate が 文字列 search より短い場合には、その文字は削除される。

(※) 置換文字の位置を示す単位はキャラクタ単位（≠バイト）である。シングルバイト文字からマルチバイト文字への置換も可能
置換できる単位は １文字単位（自然言語ではなくプログラミング言語上での1文字） で処理されるため単語から単語への文字列置換には REPLACE 関数 を使用する。

注意

TRANSLATE 関数は文字の置換処理が１文字単位で置換が行なわれるため、濁点や半濁点の半角カタカナは正しく変換できない。⇒ 半角カタカナを全角に変換する方法

半角カタカナ 「パピプペポ」 を 「ぱぴぷぺぽ」 に TRANSLATE しようとすると… 「パピプペポ」 は一見すると謎の文字列 「ぱぴぷぴぽぴぴぴ」 になってしまう。

TRANSLATE 使用例

文字の変換は「１文字 ⇔ １文字」で行なわれる。Unicode の合成文字含む

SQL> set null '<NULL>'
 
select
 str, srch, trans, translate(str, srch, trans)
from (
select 'abccba' str,  'ab' srch, '変換' trans from dual union all
select 'abccba',  'abc', '文字' from dual union all
select 'abc',  'ab', '文字単位' from dual union all
select null, null, '△' from dual union all
select '三〇四五円', '一二三四五六七八九〇円', '1234567890' from dual union all
select 'あいうえお', 'あいうえお', 'アイウエオ' from dual union all
select 'ぱぴぷぺぽ', 'ぱぴぷぺぽ', 'パピプペポﾟ' from dual union all
select 'ﾊﾟﾋﾟﾌﾟﾍﾟﾎﾟ', 'ﾊﾟﾋﾟﾌﾟﾍﾟﾎﾟ', 'ぱぴぷぺぽ' from dual union all
select decompose('ぱぴぷぺぽ'), decompose('ぱぴぷぺぽ'), 'ぱぴぷぺぽ' from dual
);
 
STR          SRCH                     TRANS        TRANSLATE(STR,SRCH,TRANS)
------------ ------------------------ ------------ -------------------------
abccba       ab                       変換         変換cc換変
abccba       abc                      文字         文字字文
abc          ab                       文字単位     文字c
<NULL>       <NULL>                   △           <NULL
三〇四五円   一二三四五六七八九〇円   1234567890   3045
あいうえお   あいうえお               アイウエオ   アイウエオ
ぱぴぷぺぽ   ぱぴぷぺぽ               パピプペポﾟ  パピプペポ
ﾊﾟﾋﾟﾌﾟﾍﾟﾎﾟ   ﾊﾟﾋﾟﾌﾟﾍﾟﾎﾟ               ぱぴぷぺぽ   ぱぴぷぴぽぴぴぴ
ぱぴぷぺぽ   ぱぴぷぺぽ               ぱぴぷぺぽ   ぱぴぷぴぽぴぴぴ

⇒ SQL*Plus の NULL 表示

NULL を引数にすると NULL（長さ0の文字列でも同様に NULL になる）

TRANSLATE('123', '456', NULL) ⇒ NULL

改行コード、または、任意の文字列をすべて削除する豆知識

TRANSLATE(文字列, CHR(10), '') という変換は、上述のとおり、改行コードを削除する処理ではなく、どのようなの文字列も NULL に変換する記述となる。
これを回避するために 変換前後の文字列の先頭に 任意の同じ文字 を配置する。

改行コード（LF のみ）の削除を行ないたい場合には
TRANSLATE (改行を含む文字列, '#' || CHR(10), '#' ) とすることで '#' は '#' に置換されるが事実上は変化がなく、 改行(LF) だけが削除される。

---

SQL 関数 - TRANSLATE 関連

• 文字からコードポイントを求める ⇒ ascii ( n )
• 文字コードを変換する ⇒ convert ( e, d, s )
• 文字列を単語レベルで置換する ⇒ replace ( s1, s2, r )
• 文字列を反転・逆さ読みする ⇒ reverse_multi ( s )
• 正規表現でマッチする文字を置換する ⇒ regexp_replace ( s, p )
• 半角カナを全角かなに変換する ⇒ to_full_kana ( s, t )
• 全角空白と半角スペースを取り除く ⇒ trim_multi ( s, r )