SQL（Oracle）で文字列から前後空白を除去したい

■　SQLで空白を除去したい

文字列の前後の空白の除いた結果を取得する必要が出たのですが、簡単なようで結構悩みました。半角、全角のスペース混在とか考えると、結構面倒なんですよね。

以下、試して見た履歴を書いていきますが、半角と全角のスペースの違いが見えずらいので、全角スペースは□で表現します。

なお、環境はOracle11gです。

■　前後空白の除去

Oracleには指定した文字を除去をするTRIM関数があるので、これを使ってみます。

SELECT TRIM(' 札幌市 白石 ')
  FROM DUAL

結果は「札幌市 白石」と前後の空白が除去されるので、一見良さそうに見えます。

問題は、TRIM関数が除去してくれるのは１バイトスペースだけです、つまり半角の空白しか除去してくれません。ためしに、２バイトスペース（全角空白）を前後に持つ文字列に対してTRIM関数を実行すると...

SELECT TRIM('□札幌市 白石□')
  FROM DUAL

結果は「□札幌市 白石□」となり、全角スペースは除去されません。

■　全角空白の除去

TRIM関数は指定した１文字を除去する関数なので、全角空白を指定して除去することが出来そうです。

SELECT TRIM('□' FROM '□札幌市 白石□')
  FROM DUAL

結果は「札幌市 白石」となり、無事に全角空白が除去できました。

しかし、まだ問題が残っています。

■　前後空白の除去（失敗）

ここまでの結果から、TRIM関数を二重に実行すれば出来ると思ったのですが、よく考えるとダメですね。

大丈夫なパターンは、これです。

SELECT TRIM(TRIM('□' FROM '□ 札幌市 白石 □'))
  FROM DUAL

この結果は「札幌市 白石」になるのですが、今度は元の文字列を「 □札幌市 白石□ 」という感じにすると...

SELECT TRIM(TRIM('□' FROM ' □札幌市 白石□ '))
  FROM DUAL

結果は「□札幌市 白石□」になり、全角空白が除去されません。

全角スペースを除去するTRIM関数が実行された時点では、対象文字列の前後にあるのは半角スペースなため、全角スペースの除去が行われず、次のTRIM関数で半角スペースが除去されています。

つまり、文字列の前後に全角と半角のスペースが混在している場合に対応しきれないってことですね。

■　前後空白の除去（成功）

どうやら、TRIM関数では無理があるようです。そこで目を付けたのは、LTRIM関数とRTRIM関数です。

LTRIM関数は“指定された全ての文字”を左側から、RTRIM関数は“指定された全ての文字”を右側から除くことが出来ます。

LTRIM関数の第二引数に、全角スペースと半角スペースを設定します。

SELECT LTRIM('□ □ □  □札幌市 白石', '□ ')
  FROM DUAL

実行結果は「札幌市 白石」となり、除去に成功しました。

今度は、RTRIM関数も加えて実行します。

SELECT RTRIM(LTRIM('□ □ □  □札幌市 白石□ □ □  □', '□ '), '□ ')
  FROM DUAL

実行結果は「札幌市 白石」です。

これで、文字列の前後から空白を除くという要件に対応できそうです。

■　補足

TRIM関数とLTRIM関数＆RTRIM関数の違いは、除去できる文字が１文字か、複数の文字を指定できるかと言う事のようです。

TRIM関数で、除去する文字を複数指定して実行してみます。

SELECT TRIM('□ ' FROM ' □札幌市 白石□ ')
  FROM DUAL

すると、「ORA-30001：切捨てセットの文字は1 つにする必要があります 」というエラーが発生しました。

切り捨てセットとは聞かない言葉ですが、言葉通り２つ以上の文字を指定して除去することは出来ないようです。

■　後書き

今回使ったTRIM関数、LTRIM関数、RTRIM関数ですが、過去何年も“空白を除去する関数”だと思っていました。本屋で売っているSQLの本とか見ても、空白を除去する関数って書かれているので、ずっと気付きませんでした。

Oracleのマニュアル（SQLリファレンス）で、よくよくLTRIM関数の説明を読んでみると「先頭文字からスキャンし始め、指定された文字をすべて削除します」ってちゃんと書いているんですよね。

...先入観って怖いです。