ltrim() function in PostgreSQL

The ltrim() function in PostgreSQL is used to remove the specified characters, special symbols and spaces on left side of the string. It is essentially a function to trim leading characters or leading space  from a string.

Syntax of ltrim() function in PostgreSQL

• string: The input string from which you want to remove characters.
• characters: A string containing all characters to be removed from the left end of the input string.

Examples of ltrim() function in PostgreSQL

1.Basic Usage remove specified characters on left side:

 

SELECT ltrim('xyxHello World', 'xy');

This removes the characters ‘x’ and ‘y’ from left ends of the string ‘xyxHello World’ and returns ‘Hello World’.

Output:

2. Trimming Whitespace on left side (leading white space)

 

SELECT ltrim('  Hello World');

By default, if you do not specify the characters to trim, ltrim() removes whitespace from left end (removes the leading whitespaces) of the string and returns ‘Hello World’.

Output:

3. Trimming Custom Characters on the left side

 

SELECT ltrim('---Hello World', '-');

Explanation: This removes the character ‘-‘ from left end of the string ‘—Hello World’ and returns ‘Hello World’.

Output:

4. Trimming Multiple Characters (special Characters) on the left side

 

SELECT ltrim('*!$!Hello World', '!*$');

Explanation: This removes the multiple special characters like  ‘$’, ‘!’, ’*’  from left end of the string and returns ‘Hello World’.

Output:

Apply ltrim() function over a column in PostgreSQL

To use the ltrim() function on a column in PostgreSQL, you can apply it directly in a SELECT statement.

Table: “users” – Lets take “users” table as example.

Selecting with ltrim()

You can use ltrim() to trim unwanted characters on the left side of the specific column when selecting data from the table:

 

SELECT *, ltrim(username, ' *#$&^') AS trimmed_username FROM users;

ltrim(username, ‘ *#$&^’): This removes leading spaces, asterisks (*), dollar ($), hash (#) etc  characters from the username column and stores the trimmed username in the separate column as shown.

Output:

Using ltrim() on a column allows you to clean up data by removing unwanted leading characters. This can be done dynamically in SELECT queries or permanently with UPDATE statements