btrim() function in PostgreSQL

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

Syntax of btrim()

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

Examples of btrim() function in PostgreSQL

1. Basic Usage remove specified characters on either side:

 

SELECT btrim('xyxHello Worldxyx', 'xy');

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

Output:

2. Trimming Whitespace

 

SELECT btrim('  Hello World  ');

By default, if you do not specify the characters to trim, btrim() removes whitespace from both ends of the string and returns ‘Hello World’.

Output:

3. Trimming Custom Characters

 

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

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

Output:

4. Trimming Multiple Characters (special Characters)

 

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

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

Output:

Apply btrim() function over a column in PostgreSQL

To use the btrim() 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 btrim()

You can use btrim() to trim unwanted characters when selecting data from the table:

 

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

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

Output:

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