PostgreSQL – TRIM Function

In PostgreSQL, the TRIM() function is used to remove the longest string consisting of spaces or any specified character from a string. By default, the TRIM() function removes all spaces (‘ ‘) if not specified  explicitly. The TRIM() function, can also be used to remove the longest string containing a character starting from the start to the end of the string.It is generally used for data cleansing.

The following is the syntax of the TRIM() function:

Syntax: TRIM([LEADING | TRAILING | BOTH] [characters] FROM string)

Example 1:

The following statement removes leading, trailing, and both leading and trailing spaces from strings:

SELECT
    TRIM (
        LEADING
        FROM
            '  Geeks ForGeeks'
    ),
    TRIM (
        TRAILING
        FROM
            'Geeks ForGeeks   '
    ),
    TRIM ('  Geeks ForGeeks  ');

Output:

Example 2:

The following statement removes the leading zero (0) from a number. As the function only accepts string values we have to use a type cast to convert the number into a string before passing it to the TRIM() function.

SELECT
    TRIM (
        LEADING '0'
        FROM
            CAST (0009100 AS TEXT)
    );

Output: