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

syntax

TRANSLATE ( string , search , translate )
return string

parameter

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

return

  • 置換した文字列

avail

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

TRANSLATE 関数の内容

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

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

注意

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

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

STEP半角カタカナ「パピプペポ」を「ぱぴぷぺぽ」に変換するプロセス
1パピプペポを文字単位に分割
2文字それぞれ対応させると…
-----
3「゜」文字は重複定義= 「゜」 は、まず 「ぴ」 に変換される。
その後、対応する文字が存在しないものは削除される・・・すると
--
結果⇒ ぱぴぷぴぽぴぴぴ

TRANSLATE 使用例

文字の変換は「1文字 ⇔ 1文字」で行なわれる。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 関連

 
アルファベット別 関数一覧 ショートカット
ABCDEFG
HILMNOP
RSTUVWX
日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ Oracle のライセンスがわからない…
Oracle Direct (ネットで聞いても最後はここで要確認)