Oracle TRIM Function: A Simple Guide

The TRIM function in Oracle SQL is a flexible and powerful tool for cleaning strings. It allows you to remove unwanted characters from the beginning (leading), the end (trailing), or both sides of a string.

It's an essential function for cleaning up data, especially data that comes from user input or external files.

What is the TRIM Function in Oracle?

The TRIM function lets you remove specific characters from the start or end of a string. By default, it removes spaces, which is its most common use.

This is useful for:

• Removing accidental spaces before or after a username or password.
• Cleaning up data imported from a CSV where extra spaces might exist.
• Removing specific non-space characters, like $ from a price string or 0 from a padded number.

TRIM Function Syntax

The full syntax for TRIM is:

TRIM( [ { LEADING | TRAILING | BOTH } ] [ trim_character ] FROM trim_source )

Let's break that down:

• LEADING | TRAILING | BOTH (Optional): This tells the function which side to trim.
  • LEADING: Removes characters from the beginning.
  • TRAILING: Removes characters from the end.
  • BOTH: Removes characters from both the beginning and end.
  • If you omit this, the default is BOTH.
• trim_character (Optional): The specific character you want to remove.
  • If you omit this, the default is a single space (' ').
• trim_source: The original string or column you want to clean (e.g., ' Hello World ').

Simplified Syntax (for spaces): If you just want to remove spaces from both sides, the syntax is very simple: TRIM(trim_source)

Oracle TRIM Function Examples

Here are two practical examples of how to use TRIM.

Example 1: Removing Leading and Trailing Spaces (Default) with TRIM

This is the most common use. Imagine a user entered their name with extra spaces, and you want to save it cleanly in the database.

Query:

SELECT 
  TRIM('   John Doe   ') AS "Cleaned Name"
FROM DUAL;

(This is the same as TRIM(BOTH ' ' FROM ' John Doe '))

Result:

Cleaned Name
------------
John Doe

Example 2: Removing a Specific Character (Leading) using TRIM

Let's say you have product codes that are sometimes padded with leading zeros (e.g., 0012345), but you want the standard number.

Query:

SELECT 
  TRIM(LEADING '0' FROM '0012345') AS "Product Code"
FROM DUAL;

Result:

Product Code
------------
12345