SQL LTRIM function

Summary: in this tutorial, you’ll learn how to use the SQL LTRIM function to return a new with specified characters removed from the start of a string.

Introduction to SQL LTRIM function #

The LTRIM function takes a string and returns a new string with specified characters removed from the start of the input string.

Here’s the syntax of the LTRIM function:

LTRIM(string, [trim_characters])Code language: SQL (Structured Query Language) (sql)

The LTRIM function takes two parameters:

string: The input string from which you want to remove the leading characters.
trim_characters: (Optional) A string of characters you want to trim. If you omit the trim_characters, the function will remove spaces by default.

The LTRIM function returns a new string with all trim_characters removed from the input string. It does not modify the original string.

In practice, you’ll find the LTRIM function helpful in cleaning string data for queries or string transformations.

We’ll use the employees table from the HR sample database to demonstrate the LTRIM function:

Removing leading spaces #

First, add a new row to the employees table with the first name and last name have a leading space:

INSERT INTO
  employees (
    employee_id,
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    job_id,
    salary,
    manager_id,
    department_id
  )
VALUES
  (
    306,
    ' John',
    ' Doe',
    '[email protected]',
    '#515.123.8191',
    '1994-06-07',
    1,
    8500.00,
    205,
    11
  );Code language: SQL (Structured Query Language) (sql)

Try it

Second, select the employee whose first_name starts with a space:

SELECT
  first_name,
  LTRIM(first_name) AS trimmed_name
FROM
  employees
WHERE
  first_name LIKE ' %';Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | trimmed_name
------------+--------------
  John      | JohnCode language: SQL (Structured Query Language) (sql)

In this query:

The WHERE clause uses the LIKE operator to match the first_name that starts with a space.
The LTRIM function removes the leading space from the first_name column.

Third, remove the leading space from the first_name column:

UPDATE employees
SET
  first_name = LTRIM(first_name)
WHERE
  first_name LIKE ' %';Code language: SQL (Structured Query Language) (sql)

Try it

Removing Specific Characters #

The following query returns the first name and phone number of the employee id 306 with the leading character # removed from the phone_number:

SELECT
  first_name,
  phone_number,
  LTRIM(phone_number, '#') AS plain_phone_number
FROM
  employees
WHERE
  employee_id = 306;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | phone_number  | plain_phone_number
------------+---------------+--------------------
 John       | #515.123.8191 | 515.123.8191Code language: SQL (Structured Query Language) (sql)

In this example, we use the LTRIM function to trim the character # from the start of the phone number.

You can remove the character # from the phone number using the LTRIM function in an UPDATE statement:

UPDATE employees
SET
  phone_number = LTRIM(phone_number, '#')
WHERE
  employee_id = 306;Code language: SQL (Structured Query Language) (sql)

Try it

Using the LTRIM function in Conditional Logic #

The following query uses the LTRIM to flag employees whose last name contains leading spaces:

SELECT
  employee_id,
  last_name,
  CASE
    WHEN last_name != LTRIM(last_name) THEN 'Has leading spaces'
    ELSE 'No leading spaces'
  END AS status
FROM
  employees
ORDER BY
  last_name;Code language: SQL (Structured Query Language) (sql)

Try it

Summary #

Use the LTRIM function to return a new string with specified characters removed from the input string.
If you omit the trim_characters, the LTRIM function defaults to removing the leading spaces.

Databases #

Was this tutorial helpful ?