Oracle LTRIM Function: A Simple Guide to Trimming Characters

When working with data, you'll often find strings with unwanted characters at the beginning, especially leading spaces. The Oracle LTRIM function is a simple and powerful tool for cleaning this data.

LTRIM stands for "Left Trim." It removes characters from the left side of a string. This guide will explain its syntax and show you two common examples.

What is the LTRIM Function in Oracle?

The LTRIM function scans a string from left to right and removes any characters that match a specific set you define. It stops removing characters as soon as it hits one that is not in the set.

This is extremely useful for:

• Removing accidental leading spaces from user input (e.g., ' John').
• Cleaning up data that has unwanted prefixes (e.g., '$$$500.00').

LTRIM Function Syntax

The syntax for the LTRIM function is simple:

LTRIM(char, [set])

Let's break down each part:

• char: This is the original string or column you want to trim (e.g., ' Hello' or product_code).
• [set] (Optional): This is the string containing all the individual characters you want to remove.
  • If you don't provide this, Oracle will automatically use a single space ' ' as the default.
  • The order of characters in the set doesn't matter. LTRIM('123Hello', '123') is the same as LTRIM('123Hello', '312').

Oracle LTRIM Function Examples

Here are two practical examples of how to use LTRIM in your Oracle SQL queries.

Example 1: Trimming Leading Spaces (Default) with LTRIM

This is the most common use of LTRIM. You have a string that has extra, unwanted spaces at the beginning, and you want to remove them.

Since we are not providing the [set] argument, LTRIM defaults to removing spaces.

Query:

SELECT LTRIM('     Hello World!') AS "Trimmed String"
FROM DUAL;

Result:

Trimmed String
---------------
Hello World!

The function removed all five leading spaces and stopped when it reached the 'H'.

Example 2: Trimming a Specific Set of Characters using LTRIM

Imagine you have a products table where the product_code column sometimes has leading zeros or 'X's that you want to remove.

You can tell LTRIM to remove any character that is either a '0' or an 'X' from the left.

Query:

-- This query mimics a table with messy product codes
SELECT
  original_code,
  LTRIM(original_code, 'X0') AS "Cleaned Code"
FROM (
  SELECT '000123' AS original_code FROM DUAL UNION ALL
  SELECT 'X0X0456' AS original_code FROM DUAL UNION ALL
  SELECT 'XX789' AS original_code FROM DUAL UNION ALL
  SELECT '501-A' AS original_code FROM DUAL
);

Result:

ORIGINAL_CODE  Cleaned Code
-------------  ------------
000123         123
X0X0456        456
XX789          789
501-A          501-A

Notice that it removed all leading 'X's and '0's. As soon as it hit a '1', '4', '7', or '5', it stopped trimming.