Oracle SQL: A Comprehensive Guide to the REPLACE and TRIM Functions

English

This article provides a thorough explanation, from basics to applications, of the frequently used REPLACE and TRIM functions for string manipulation in Oracle SQL. We have prepared abundant examples to make it easy for beginners to understand, demonstrating specific use cases utilizing the SCOTT schema.

This article uses examples based on the SCOTT sample schema. You can import and use the SCOTT sample schema by executing the following:

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger
Oracleデータベースの基礎：サンプルスキーマで簡単に始める方法
サンプルスキーマって何？まず、サンプルスキーマとは何かから始めましょう。サンプルスキーマは、Oracleデータベースに含まれる「学習用のデータベース構造とデータ」のことで、インストールすることで学習に使用できるユーザーや表が作成されます。O...
oracle-master00000.com

1. What is the REPLACE function?

The REPLACE function is used to replace specific parts of a string with another string. Using this function allows you to process strings efficiently.

Syntax

REPLACE(string, search_string, replacement_string)
  • string: The target string to operate on.
  • search_string: The string to be replaced.
  • replacement_string: The string to replace the search_string with.

Usage Examples

Example 1: Change a name in the ENAME column The following SQL statement replaces “SMITH” with “JOHNSON” in the ENAME column.

SELECT ENAME, REPLACE(ENAME, 'SMITH', 'JOHNSON') AS MODIFIED_NAME
FROM EMP;

Result:

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.

Example 2: Remove specific characters In the following example, “A” is removed from the string.

SELECT ENAME, REPLACE(ENAME, 'A', '') AS REMOVED_A
FROM EMP;

Result:

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.

Example 3: Convert a string containing numbers The REPLACE function can also be used on strings containing numbers. In the following example, “1” is replaced with “one”.

SELECT '12345' AS ORIGINAL, REPLACE('12345', '1', 'one') AS MODIFIED
FROM DUAL;

Result:

SQL> SELECT '12345' AS ORIGINAL, REPLACE('12345', '1', 'one') AS MODIFIED
  2  FROM DUAL;

ORIGI MODIFIE
----- -------
12345 one2345

2. What is the TRIM function?

The TRIM function is used to remove unnecessary characters from the start (leading) and end (trailing) of a string. It can also remove specified characters other than whitespace.

Syntax

TRIM([char_to_remove FROM] string)
  • char_to_remove: The specific character to remove (if omitted, whitespace is the target).
  • string: The target string to operate on.

Usage Examples

Example 1: Remove whitespace In the following example, leading and trailing spaces are removed.

SELECT  '  SATO  ' AS original_text, TRIM('  SATO  ') AS trimmed_text
FROM DUAL;

Result:

SQL> SELECT  '  SATO  ' AS original_text, TRIM('  SATO  ') AS trimmed_text
  2  FROM DUAL;

ORIGINAL TRIM
-------- ----
  SATO   SATO

Example 2: Remove specific characters In the following example, the character “E” is removed from the ends of the string.

SELECT ENAME, TRIM('E' FROM ENAME) AS TRIMMED_NAME
FROM EMP
WHERE ENAME='BLAKE';

Result:

SQL> SELECT ENAME, TRIM('E' FROM ENAME) AS TRIMMED_NAME
  2  FROM EMP
  3  WHERE ENAME='BLAKE';

ENAME           TRIMMED_NA
--------------- ----------
BLAKE           BLAK

Example 3: Remove specific characters from both ends Using the TRIM function, remove specific characters from both ends of a string.

SELECT '***HELLO***' AS ORIGINAL, TRIM('*' FROM '***HELLO***') AS CLEANED
FROM DUAL;

Result:

SQL> SELECT '***HELLO***' AS ORIGINAL, TRIM('*' FROM '***HELLO***') AS CLEANED
  2  FROM DUAL;

ORIGINAL    CLEAN
----------- -----
***HELLO*** HELLO

3. Combining REPLACE and TRIM Functions

By combining the REPLACE and TRIM functions, more advanced string manipulation is possible.

Example: Remove unnecessary characters and trim whitespace In the following SQL statement, “A” is removed (from the entire string), and then leading and trailing spaces are removed.

SELECT ENAME, TRIM(REPLACE(ENAME, 'A', '')) AS CLEANED_NAME
FROM EMP;

Result:

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. Common Mistakes and Countermeasures

Common Mistakes with REPLACE

Search string not found: If the target string does not match, the original string is returned as-is. Check if the search string is correct.

SELECT REPLACE('HELLO', 'X', 'Y') AS RESULT FROM DUAL;

Result:

SQL> SELECT REPLACE('HELLO', 'X', 'Y') AS RESULT FROM DUAL;

RESUL
-----
HELLO

Common Mistakes with TRIM

Omitting the character to remove: When removing characters other than whitespace, you must explicitly specify the character to be removed.

SELECT TRIM('X' FROM 'XXXHELLOXXX') AS RESULT FROM DUAL;

Result:

SQL> SELECT TRIM('X' FROM 'XXXHELLOXXX') AS RESULT FROM DUAL;

RESUL
-----
HELLO

5. Practical Business Use Cases

Data Cleansing

You can utilize the REPLACE and TRIM functions to remove unnecessary spaces or special characters from business data.

SELECT TRIM(REPLACE(COLUMN_NAME, 'Specific_Character', '')) AS CLEANED_COLUMN
FROM TABLE_NAME;

Standardizing Data Formats

You can use the REPLACE function to standardize formats for things like phone numbers or IDs.

SELECT REPLACE('123-456-7890', '-', '') AS PHONE_NUMBER
FROM DUAL;

Result:

SQL> SELECT REPLACE('123-456-7890', '-', '') AS PHONE_NUMBER
  2  FROM DUAL;

PHONE_NUMB
----------
1234567890

6. Summary

  • The REPLACE function is useful for partial substitution within strings.
  • The TRIM function is helpful for removing unnecessary whitespace or specific characters from the ends.
  • Combining both allows for flexible string manipulation.
  • To avoid common mistakes, check the syntax and parameters before use.
  • When using them in actual business scenarios, customize the SQL to match the data being processed.

Try using these functions to manipulate data efficiently!

[reference]
Oracle Database SQL Language Reference, 19c

Oracle SELECT文の基本を解説
SELECT文はOracleデータベースを操作する上で最も基本的かつ重要なSQL文です。この文を使用することで、テーブルから必要な情報を抽出したり、条件を指定してデータを絞り込むことができます。本記事では、SELECT文の構文、基本例、応用...
oracle-master00000.com

コメント

Copied title and URL