PostgreSQL LTRIM Function

Summary: in this tutorial, you’ll learn to use the PostgreSQL LTRIM() function to remove unwanted characters from the start of a string.

Introduction to PostgreSQL LTRIM() function #

The LTRIM() function removes specified characters from the start of a string.

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

LTRIM(string [, characters])Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The LTRIM() function takes two parameters:

• string the input string you want to remove characters.
• characters is the characters you want to remove from the start of the input string.

The LTRIM() function returns a new string with the characters removed from the start of the input string.

It returns NULL if the string and/or characters is NULL.

Basic PostgreSQL LTRIM function examples #

The following query uses the LTRIM() function to remove the dollar sign ($) from a string:

SELECT
  LTRIM('$12.3', '$') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
 12.3Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To remove the dollar sign ($) and convert the value to a number, you can combine the LTRIM() function with the cast operator (::):

SELECT
  LTRIM('$12.3', '$')::DEC amount;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 amount
--------
   12.3Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following query uses the LTRIM() function to remove leading spaces from a string:

SELECT
  LTRIM(' pgtutorial.com', '') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     result
-----------------
  pgtutorial.comCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

It’s equivalent to the following statement that does not use a space as the second parameter:

SELECT
  LTRIM(' pgtutorial.com') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     result
----------------
 pgtutorial.comCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using LTRIM with table data #

First, create a new table called stock_valuations to store stock valuations:

CREATE TABLE IF NOT EXISTS stock_valuations(
   product_id INT PRIMARY KEY,
   amount VARCHAR
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Next, insert some rows into the stock_valuations table:

INSERT INTO
  stock_valuations (product_id, amount)
VALUES
  (1, '$999.99'),
  (2, '$1099.99'),
  (3, '$899.99')
RETURNING
  *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Then, remove the dollar sign$ from the start of amounts when retrieving data from the stock_valuations table:

SELECT
  product_id,
  LTRIM(amount, '$') amount
FROM
  stock_valuations
ORDER BY
  product_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id | amount
------------+---------
          1 | 999.99
          2 | 1099.99
          3 | 899.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, remove the dollar sign $, cast the amounts into decimals, and calculate the sum:

SELECT
  SUM(LTRIM(amount, '$')::DEC)
FROM
  stock_valuations;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   sum
---------
 2999.97Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

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