LTRIM() Function in Oracle

In Oracle, the LTRIM() function allows you to trim the left part of a string. By default, it trims white space, but you can optionally specify a different character or characters to trim.

Syntax

The syntax goes like this:

LTRIM(char [, set ])

Where char and set can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

LTRIM removes from the left end of char all of the characters contained in set. If you do not specify set, then it defaults to a single blank.

Example

Here’s a simple example to demonstrate:

SELECT LTRIM('     Cat')
FROM DUAL;

Result:

   LTRIM('CAT') 
_______________ 
Cat            

In this case I didn’t specify which character/s to trim, and so each single blank was trimmed from the left part of the string.

Here it is again when compared to the original (non-padded) string:

SELECT '     Cat'
FROM DUAL
UNION ALL
SELECT LTRIM('     Cat')
FROM DUAL;

Result:

      'CAT' 
___________ 
     Cat    
Cat        

Specify a Character

In this example I specify a character to trim:

SELECT LTRIM('...Cat...', '.')
FROM DUAL;

Result:

   LTRIM('...CAT...','.') 
_________________________ 
Cat...                    

So we can see that the character was trimmed from the left side but not the right side. To trim the right side, use RTRIM() or TRIM().

Note that LTRIM() only trims the character/s in the set up until there’s a character that’s not in the set. Here’s an example of what I mean:

SELECT LTRIM('...A...Cat', '.')
FROM DUAL;

Result:

   LTRIM('...A...CAT','.') 
__________________________ 
A...Cat                   

However, if we include A in the set, then we get a different result:

SELECT LTRIM('...A...Cat', '.A')
FROM DUAL;

Result:

   LTRIM('...A...CAT','.A') 
___________________________ 
Cat                        

Database Example

Here’s an example of trimming the left part of the values in a database column:

SELECT 
    country_name,
    LTRIM(country_name, 'Ar') AS Trimmed
FROM countries
FETCH FIRST 5 ROWS ONLY;

Result:

   COUNTRY_NAME     TRIMMED 
_______________ ___________ 
Argentina       gentina     
Australia       ustralia    
Belgium         Belgium     
Brazil          Brazil      
Canada          Canada     

Null Values

If any of the arguments is null the result is null:

SET NULL 'null';

SELECT 
    LTRIM(null, 3),
    LTRIM(' Cat', null),
    LTRIM(null, null)
FROM DUAL;

Result:

   LTRIM(NULL,3)    LTRIM('CAT',NULL)    LTRIM(NULL,NULL) 
________________ ____________________ ___________________ 
null             null                 null               

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Trimming Empty Strings

Passing an empty string as the second argument results in null:

SET NULL 'null';

SELECT LTRIM('   Cat', '')
FROM DUAL;

Result:

   LTRIM('CAT','') 
__________________ 
null              

But adding a single space to the empty string changes that, and trims any blank spaces from the left of the string:

SELECT LTRIM('   Cat', ' ')
FROM DUAL;

Result:

   LTRIM('CAT','') 
__________________ 
Cat               

Incorrect Argument Count

Calling LTRIM() without passing any arguments returns an error:

SELECT LTRIM()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LTRIM()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

And passing the wrong number of arguments results in an error:

SELECT LTRIM('Cat', 1, '>')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LTRIM('Cat', 1, '>')
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action: