PostgreSQL TRIM Function (original) (raw)

Last Updated : 15 Jul, 2025

The **TRIM() function in PostgreSQL is an essential tool for removing unwanted characters from strings. Whether we're working with user inputs, formatting text, or performing data cleansing operations, **TRIM() is an invaluable function for managing string data.

This article will provide an in-depth look at how to use the **TRIM() function, including **syntax, **examples, and practical use cases while focusing on **SEO-optimized keywords for PostgreSQL **string functions and **data manipulation.

PostgreSQL TRIM Function

The TRIM() function is used to remove specified characters from the start, end, or both ends of a string. By default, **TRIM() removes spaces, but we can specify any character we would like to remove. This makes it **highly flexible for cleaning up text and formatting data for easier manipulation or presentation.

In PostgreSQL, the **TRIM() function is commonly used in scenarios like **removing unnecessary spaces from user inputs, cleansing raw data before inserting it into tables, or formatting text for better readability.

**Syntax

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

**Key Terms

• **LEADING: Removes the specified characters from the beginning of the string.
• **TRAILING: Removes characters from the end of the string.
• **BOTH: Removes characters from both the beginning and end of the string.
• **characters (optional): The characters you want to remove. If not specified, it removes spaces by default.
• **string: The input string from which you want to remove characters.

**Key Points

• **Default Behavior: If no specific characters are mentioned, the TRIM() function removes spaces by default.
• **Removing Specific Characters: You can specify a character or a set of characters to be removed.
• *Trim Direction: We can specify whether to trim characters from the start (*LEADING), end (TRAILING), or both ends (BOTH**) of the string.

PostgreSQL TRIM Function Examples

Let us take a look at some of the examples of the **TRIM() Function in **PostgreSQL to better understand the concept, including various use cases such as **removing spaces, **leading zeros, and handling **complex data formatting.

**Example 1: Removing Leading, Trailing, and Both Leading and Trailing Spaces

The following statement **removes leading, **trailing, and both leading and trailing spaces from strings, ensuring that any unwanted white spaces are cleaned up for **consistent data formatting.

**Query:

SELECT
TRIM(LEADING FROM ' Geeks ForGeeks') AS leading_trimmed,
TRIM(TRAILING FROM 'Geeks ForGeeks ') AS trailing_trimmed,
TRIM(' Geeks ForGeeks ') AS both_trimmed;

**Output

**Explanation:

• leading_trimmed : 'Geeks ForGeeks' (removes leading spaces)
• trailing_trimmed : 'Geeks ForGeeks' (removes trailing spaces)
• both_trimmed : 'Geeks ForGeeks' (removes both leading and trailing spaces)

**Example 2: Removing Leading Zeros from a Number

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.

**Query:

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

**Output

**Explanation:

The **TRIM() function **removes the leading zeros from the number string, resulting in "**9100."

Example 3: Combining TRIM() with Other String Functions

We can use **TRIM() in combination with other functions like **UPPER() or **LOWER() to clean and format text simultaneously.

**Query:

SELECT
TRIM(BOTH FROM UPPER(' PostgreSQL Database ')) AS formatted_text;

**Output

**Explanation:

This query first converts the **string to uppercase and then removes spaces from both ends

Important Points About PostgreSQL TRIM() Function

• The TRIM() function can be combined with other string functions for more complex data manipulations. For example, combining**TRIM()**with**UPPER() to trim spaces and convert a string to uppercase.
• When performing data cleansing on a large dataset, the TRIM() function can be used in an **UPDATE statement to clean up the data.
• When no specific character is specified, the **TRIM()**function removes white spaces by default.
• The TRIM() function can be used on numbers and other data types by first converting them to strings.

Conclusion

In conclusion, the **TRIM() function in PostgreSQL is a powerful tool for removing unwanted characters from strings, making it an essential part of our **data cleansing toolkit. Whether we're dealing with **whitespace issues, formatting text for **readability, or **cleaning raw data for analysis, **TRIM() allows us to easily manage string data and improve our **database's integrity.