社内SEの徒然なる日記

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

■ SQLで空白を除去したい

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

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

なお、環境はOracle11gです。

■ 前後空白の除去

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


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


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

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


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


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

■ 全角空白の除去

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


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関数の違いは、除去できる文字が1文字か、複数の文字を指定できるかと言う事のようです。

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

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


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

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

■ 後書き

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

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

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

スポンサードリンク

PageTop

コメント


管理者にだけ表示を許可する