Categories:

String & Binary Functions (General)

LTRIM

Removes leading characters, including whitespace, from a string.

See also:

RTRIM , TRIM

Syntax

LTRIM( <expr> [, <characters> ] )
Copy

Arguments

expr

The string expression to be trimmed.

characters

One or more characters to remove from the left side of expr.

The default value is ' ' (a single blank space character), i.e. if no characters are specified, only blank spaces are removed.

Usage Notes

  • The characters in characters can be specified in any order.

  • To remove whitespace, the characters must be explicitly included in the argument. For example, ' $.' removes all leading blank spaces, dollar signs, and periods from the input string.

    Note that this does not remove other whitespace characters (tabulation characters, end-of-line characters, etc.), which also must be explicitly specified.

Collation Details

Collation is supported when the optional second argument is omitted, or when it contains only constant whitespace.

The collation specification of the returned value is the same as the collation specification of the first argument.

Examples

Remove leading ‘0’ and ‘#’ characters from a string:

SELECT LTRIM('#000000123', '0#');
+---------------------------+
| LTRIM('#000000123', '0#') |
|---------------------------|
| 123                       |
+---------------------------+
Copy

Remove leading blank spaces from strings, including strings with non-ASCII character. This example encloses the strings in > and < characters to help visualize the whitespace:

SELECT CONCAT('>', CONCAT(v, '<')), CONCAT('>', CONCAT(LTRIM(v), '<')) FROM tr;

-----------------------------+------------------------------------+
 concat('>', concat(v, '<')) | concat('>', concat(ltrim(v), '<')) |
-----------------------------+------------------------------------+
 >  <                        | ><                                 |
 >  asd  <                   | >asd  <                            |
 >  asd<                     | >asd<                              |
 >  éché, la lé  <           | >éché, la lé  <                    |
 > <                         | ><                                 |
 ><                          | ><                                 |
 >asd  <                     | >asd  <                            |
 [NULL]                      | [NULL]                             |
-----------------------------+------------------------------------+
Copy

This example is similar to the preceding, but includes “whitespace” characters beyond just the blank character; specifically, this uses the 6 characters that the C programming language considers “whitespace”:

Create and fill a table:

CREATE TABLE c_compatible_whitespace (whitespace_char VARCHAR);
INSERT INTO c_compatible_whitespace (whitespace_char) SELECT 
    CHR(32) || -- Blank
    CHR(13) || -- Carriage Return
    CHR(12) || -- Form Feed
    CHR(10) || -- Line Feed
    CHR(11) || -- Vertical Tab
    CHR(09)    -- tab (aka Horizontal Tab)
    ;

CREATE TABLE t1 (V VARCHAR);
INSERT INTO t1 (v) VALUES
   ('NoBlanks'),
   (' OneLeadingBlank'),
   ('OneTrailingBlank '),
   (' OneLeadingAndOneTrailingBlank ')
   ;
INSERT INTO t1 (v) SELECT 
    (CHR(09) || -- tab (aka Horizontal Tab)
     CHR(10) || -- Line Feed
     CHR(11) || -- Vertical Tab
     CHR(12) || -- Form Feed
     CHR(13) || -- Carriage Return
     CHR(32)    -- Blank 
      || 'Leading whitespace'
    )
   ;
Copy

This is the output (not surprisingly, some of the non-blank whitespace characters make this output a little difficult to read):

SELECT '>' || v || '<' AS Original,
       '>' || LTRIM(v, (SELECT whitespace_char FROM c_compatible_whitespace)) || '<' AS LTrimmed
    FROM t1;
+-----------------------------------+----------------------------------+
| ORIGINAL                          | LTRIMMED                         |
|-----------------------------------+----------------------------------|
| >NoBlanks<                        | >NoBlanks<                       |
| > OneLeadingBlank<                | >OneLeadingBlank<                |
| >OneTrailingBlank <               | >OneTrailingBlank <              |
| > OneLeadingAndOneTrailingBlank < | >OneLeadingAndOneTrailingBlank < |
| >	                                                                      | >Leading whitespace<             |
| 
 Leading whitespace<              |                                  |
+-----------------------------------+----------------------------------+
Copy