- Categories:
String & Binary Functions (General)
TRIM¶
Removes leading and trailing characters from a string.
Note
To remove characters in a string, you can use the REPLACE function.
Syntax¶
TRIM( <expr> [, <characters> ] )
Arguments¶
expr
A string expression to be trimmed.
characters
One or more characters to remove from the left and right side of
expr
.The default value is
' '
(a single blank space character), i.e. if no characters are specified, all leading and trailing blank spaces are removed.
Returns¶
This function returns a value of VARCHAR data type or NULL.
Usage notes¶
The characters in
characters
can be specified in any order.To remove whitespace, the characters must be explicitly included in the argument. For example,
' $.'
removes all leading and trailing blank spaces, dollar signs, and periods from the input string.Note that this does not remove other whitespace characters (tabulation characters, end-of-line characters, etc.), which also must be explicitly specified.
Collation Details¶
Collation is supported when the optional second argument is omitted, or when it contains only constant whitespace.
The collation specification of the returned value is the same as the collation specification of the first argument.
Examples¶
Remove leading and trailing *
and -
characters from a string:
SELECT TRIM('*-*ABC-*-', '*-');
+-------------------------+
| TRIM('*-*ABC-*-', '*-') |
|-------------------------|
| ABC |
+-------------------------+
Remove leading and trailing whitespace from a string. This example encloses
the strings in >
and <
characters to help visualize the whitespace.
It also shows that the function returns NULL for NULL input.
CREATE OR REPLACE TABLE test_trim_function(column1 VARCHAR);
INSERT INTO test_trim_function VALUES (' Leading Spaces'), ('Trailing Spaces '), (NULL);
SELECT CONCAT('>', CONCAT(column1, '<')) AS original_values,
CONCAT('>', CONCAT(TRIM(column1), '<')) AS trimmed_values
FROM test_trim_function;
+---------------------+-------------------+
| ORIGINAL_VALUES | TRIMMED_VALUES |
|---------------------+-------------------|
| > Leading Spaces< | >Leading Spaces< |
| >Trailing Spaces < | >Trailing Spaces< |
| NULL | NULL |
+---------------------+-------------------+