PostgreSQL LTRIM() Function

Summary: in this tutorial, you will learn how to use the PostgreSQL LTRIM() function to remove specified characters from the beginning of a string.

Introduction to PostgreSQL LTRIM() function

The LTRIM() function allows you to remove specified characters from the beginning of a string.

Here’s the syntax of the LTRIM() function:

LTRIM(string, character)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• string is the input string that you want to remove characters.
• character specifies the characters you want to remove from the beginning of the string. The character parameter is optional. It defaults to space.

The LTRIM() function returns the string with all leading characters removed.

To remove both leading and trailing characters from a string, you use the TRIM() function.

To remove the trailing characters from a string, you use the RTRIM() function.

PostgreSQL LTRIM() function examples

Let’s explore some examples of using the LTRIM() function.

1) Basic PostgreSQL LTRIM() function example

The following example uses the LTRIM() function to remove the # from the beginning of the string #postgres:

SELECT LTRIM('#postgres', '#');Code language: SQL (Structured Query Language) (sql)

Output:

  ltrim
----------
 postgres
(1 row)Code language: SQL (Structured Query Language) (sql)

2) Using the PostgreSQL LTRIM() function to remove leading spaces

The following example uses the LTRIM() function to remove all the spaces from the string ' PostgreSQL':

SELECT LTRIM('   PostgreSQL');Code language: SQL (Structured Query Language) (sql)

Output:

   ltrim
------------
 PostgreSQL
(1 row)Code language: SQL (Structured Query Language) (sql)

Since the default of the second argument of the LTRIM() function is space, we don’t need to specify it.

3) Using the LTRIM() function with table data example

First, create a new table called articles and insert some rows into it:

CREATE TABLE articles(
   id SERIAL PRIMARY KEY,
   title VARCHAR(255) NOT NULL
);

INSERT INTO articles(title)
VALUES
   ('   Mastering PostgreSQL string functions'),
   (' PostgreSQL LTRIM() function')
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

 id |                  title
----+------------------------------------------
  1 |    Mastering PostgreSQL string functions
  2 |  PostgreSQL LTRIM() function
(2 rows)Code language: SQL (Structured Query Language) (sql)

Second, update the titles by removing the leading spaces using the LTRIM() function:

UPDATE articles
SET title = LTRIM(title);Code language: SQL (Structured Query Language) (sql)

Output:

UPDATE 2Code language: SQL (Structured Query Language) (sql)

The output indicates that two rows were updated.

Third, verify the updates:

SELECT * FROM articles;Code language: SQL (Structured Query Language) (sql)

Output:

 id |                 title
----+---------------------------------------
  1 | Mastering PostgreSQL string functions
  2 | PostgreSQL LTRIM() function
(2 rows)Code language: SQL (Structured Query Language) (sql)

Summary

• Use LTRIM() function to remove all specified characters from the beginning of a string.