Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL TRIM

The PostgreSQL `TRIM` function is a string function used to remove specified leading and/or trailing characters from a string. By default, it removes whitespace from the beginning and end of a string.

Usage

The `TRIM` function is used to clean up strings by removing unwanted characters that can affect data consistency and formatting. It is particularly useful when processing data input where extra spaces or specific characters need to be eliminated.

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

In this syntax, `BOTH`, `LEADING`, or `TRAILING` specify which side(s) of the string to trim. If no direction is specified, `BOTH` is assumed. If the `characters` argument is not provided, the function defaults to trimming spaces. `string` is the target string. If the `string` parameter is NULL, the function will return NULL.

Examples

1. Basic Trim

SELECT TRIM('  hello  ');

This example removes spaces from both ends of the string `' hello '`, resulting in `'hello'`.

2. Trim Specific Characters

SELECT TRIM(BOTH 'x' FROM 'xxxhelloxxx');

Here, the `TRIM` function removes 'x' from both ends of the string, returning `'hello'`.

3. Leading and Trailing Trim

SELECT TRIM(LEADING 'a' FROM 'aaabca');

This example removes leading 'a' characters from `'aaabca'`, resulting in `'bca'`.

4. Trim without Arguments

SELECT TRIM('  example  ');

This example demonstrates the default behavior of the `TRIM` function, removing whitespace from both ends of the string, resulting in `'example'`.

Tips and Best Practices

  • Default to whitespace. Use `TRIM` without specifying characters if you only need to remove whitespace.
  • Specify direction wisely. Use `LEADING` or `TRAILING` when you need to trim from only one side of the string for efficiency.
  • Combine with other functions. Use `TRIM` in conjunction with other string functions like `UPPER` or `LOWER` for more complex string processing.
  • Consider data uniformity. Regularly trim input data to ensure uniformity when storing or comparing strings in a database.
  • Understand NULL behavior. Be aware that if the `string` parameter is NULL, the result will be NULL.
  • Performance considerations. Be mindful of the potential performance impact when using `TRIM` in large datasets or complex queries.
  • Compare with similar functions. Consider how `TRIM` differs from `LTRIM` or `RTRIM`, which only remove characters from the left or right end, respectively.