Oracle SQLで文字列操作を行う際に頻繁に使用されるreplace関数とtrim関数について、基礎から応用まで徹底的に解説します。本記事では、初心者でも分かりやすいように例を豊富に用意し、scottスキーマを活用して具体的な使用例を示します。
本記事ではSCOTTサンプルスキーマを使用した例で解説します。
SCOTTサンプルスキーマは以下を実行することでインポートおよび使用が可能です。
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
1. replace関数とは
replace関数は、文字列中の特定の部分を別の文字列に置き換えるために使用されます。この関数を使用することで、効率的に文字列を加工できます。
構文
REPLACE(文字列, 検索文字列, 置換文字列)- 文字列: 操作対象の文字列。
- 検索文字列: 置換する対象の文字列。
- 置換文字列: 検索文字列を置き換える文字列。
使用例
例1: ENAME列の名前を変更
以下のSQL文では、ENAME列に含まれる”SMITH”を”JOHNSON”に置き換えます。
SELECT ENAME, REPLACE(ENAME, 'SMITH', 'JOHNSON') AS MODIFIED_NAME
FROM EMP;
結果:
SQL> SELECT ENAME, REPLACE(ENAME, 'SMITH', 'JOHNSON') AS MODIFIED_NAME
2 FROM EMP;
ENAME MODIFIED_NAME
--------------- -----------------------------------------------
SMITH JOHNSON ★
ALLEN ALLEN
WARD WARD
JONES JONES
MARTIN MARTIN
BLAKE BLAKE
CLARK CLARK
SCOTT SCOTT
KING KING
TURNER TURNER
ADAMS ADAMS
JAMES JAMES
FORD FORD
MILLER MILLER
14 rows selected.
例2: 特定の文字を削除
以下の例では、文字列から”A”を削除します。
SELECT ENAME, REPLACE(ENAME, 'A', '') AS REMOVED_A
FROM EMP;
結果:
SQL> SELECT ENAME, REPLACE(ENAME, 'A', '') AS REMOVED_A
2 FROM EMP;
ENAME REMOVED_A
--------------- ----------
SMITH SMITH
ALLEN LLEN ★
WARD WRD ★
JONES JONES
MARTIN MRTIN ★
BLAKE BLKE ★
CLARK CLRK ★
SCOTT SCOTT
KING KING
TURNER TURNER
ADAMS DMS ★
JAMES JMES ★
FORD FORD
MILLER MILLER
14 rows selected.
例3: 数値を含む文字列の変換
replace関数は数値を含む文字列にも使用できます。以下の例では、”1″を”one”に置き換えます。
SELECT '12345' AS ORIGINAL, REPLACE('12345', '1', 'one') AS MODIFIED
FROM DUAL;
結果:
SQL> SELECT '12345' AS ORIGINAL, REPLACE('12345', '1', 'one') AS MODIFIED
2 FROM DUAL;
ORIGI MODIFIE
----- -------
12345 one2345
2. trim関数とは
trim関数は、文字列の先頭と末尾にある不要な文字を削除するために使用されます。空白以外の指定した文字も削除可能です。
構文
TRIM([削除対象文字 FROM] 文字列)- 削除対象文字: 削除する特定の文字（省略すると空白が対象）。
- 文字列: 操作対象の文字列。
使用例
例1: 空白を削除
以下の例では、ENAME列の先頭と末尾の空白を削除します。
SELECT ' SATO ' AS original_text, TRIM(' SATO ') AS trimmed_text
FROM DUAL;
結果:
SQL> SELECT ' SATO ' AS original_text, TRIM(' SATO ') AS trimmed_text
2 FROM DUAL;
ORIGINAL TRIM
-------- ----
SATO SATO
例2: 特定の文字を削除
以下の例では、文字列から”E”を削除します。
SELECT ENAME, TRIM('E' FROM ENAME) AS TRIMMED_NAME
FROM EMP
WHERE ENAME='BLAKE';
結果:
SQL> SELECT ENAME, TRIM('E' FROM ENAME) AS TRIMMED_NAME
2 FROM EMP
3 WHERE ENAME='BLAKE';
ENAME TRIMMED_NA
--------------- ----------
BLAKE BLAK
例3: 両端の特定文字を削除
TRIM関数を使用して、文字列の両端にある特定の文字を削除します。
SELECT '***HELLO***' AS ORIGINAL, TRIM('*' FROM '***HELLO***') AS CLEANED
FROM DUAL;
結果:
SQL> SELECT '***HELLO***' AS ORIGINAL, TRIM('*' FROM '***HELLO***') AS CLEANED
2 FROM DUAL;
ORIGINAL CLEAN
----------- -----
***HELLO*** HELLO
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
3. replace関数とtrim関数の組み合わせ
replace関数とtrim関数を組み合わせることで、より高度な文字列操作が可能です。
例: 不要な文字を削除し空白をトリミング
以下のSQL文では、”A”を削除し、さらに先頭と末尾の空白を取り除きます。
SELECT ENAME, TRIM(REPLACE(ENAME, 'A', '')) AS CLEANED_NAME
FROM EMP;
結果:
SQL> SELECT ENAME, TRIM(REPLACE(ENAME, 'A', '')) AS CLEANED_NAME
2 FROM EMP;
ENAME CLEANED_NA
--------------- ----------
SMITH SMITH
ALLEN LLEN
WARD WRD
JONES JONES
MARTIN MRTIN
BLAKE BLKE
CLARK CLRK
SCOTT SCOTT
KING KING
TURNER TURNER
ADAMS DMS
JAMES JMES
FORD FORD
MILLER MILLER
14 rows selected.
4. よくあるミスとその対策
replace関数のよくあるミス
- 検索文字列が見つからない場合: 対象文字列が一致しない場合、元の文字列がそのまま返されます。検索文字列が正しいか確認してください。
SELECT REPLACE('HELLO', 'X', 'Y') AS RESULT FROM DUAL;
結果:
SQL> SELECT REPLACE('HELLO', 'X', 'Y') AS RESULT FROM DUAL;
RESUL
-----
HELLO
trim関数のよくあるミス
- 削除対象文字の省略: 空白以外の文字を削除する際には、削除対象文字を明示的に指定する必要があります。
SELECT TRIM('X' FROM 'XXXHELLOXXX') AS RESULT FROM DUAL;
結果:
SQL> SELECT TRIM('X' FROM 'XXXHELLOXXX') AS RESULT FROM DUAL;
RESUL
-----
HELLO
5. 業務での活用例
データクレンジング
業務データで不要なスペースや特殊文字を削除する際に、replaceとtrim関数を活用できます。
SELECT TRIM(REPLACE(COLUMN_NAME, '特定の文字', '')) AS CLEANED_COLUMN
FROM TABLE_NAME;
データフォーマットの統一
replace関数を使用して、電話番号やIDなどのフォーマットを統一できます。
SELECT REPLACE('123-456-7890', '-', '') AS PHONE_NUMBER
FROM DUAL;
結果:
SQL> SELECT REPLACE('123-456-7890', '-', '') AS PHONE_NUMBER
2 FROM DUAL;
PHONE_NUMB
----------
1234567890
6. まとめ
replace関数は文字列内の部分置換に便利です。trim関数は不要な空白や特定文字の削除に役立ちます。- 両者を組み合わせることで、柔軟な文字列操作が可能です。
- よくあるミスを避けるため、使用前に構文やパラメータを確認しましょう。
- 実際の業務シナリオで活用する際には、処理対象データに合わせてSQLをカスタマイズしてください。
これらの関数を活用して、効率的にデータを操作してみてください！
[参考]
Oracle Database SQL言語リファレンス 19c
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
コメント