Sign inTry Free

TRIM

On this page

Removes padding from the ends of the given string.

Syntax

TRIM ([[BOTH | LEADING | TRAILING] [padding] FROM] str)

Arguments

  • padding: any string or binary object (optional, defaults to " ")

  • str: any string or binary object

If no keyword is specified, defaults to BOTH.

Return Type

String or binary

Remarks

TRIM is now multibyte-safe. This means if str is a string, then the result of an operation is either valid string (if padding is valid in collation of str) or is an unmodified str.

Examples

Note

This function removes only space " " characters. Other whitespace characters like tab \t, newline \n, etc are preserved.

SELECT TRIM('    ohai     ') AS t,
  TRIM(LEADING FROM '    ohai     ') AS l,
  TRIM(TRAILING FROM '    ohai     ') AS r;
+------+-----------+----------+
| t    | l         | r        |
+------+-----------+----------+
| ohai | ohai      |     ohai |
+------+-----------+----------+
SELECT TRIM("abra" FROM "abracadabra") AS t;
+------+
| t    |
+------+
| cad  |
+------+

utf8_general_ci is the default collation_server setting. The results in the following examples will behave differently if a different collation set is selected.

SELECT @@collation_server;
+--------------------+ 
|@@collation_server  |
+--------------------+
| utf8_general_ci    |
+--------------------+
SELECT HEX("Ā敥");
+--------------+
| HEX("Ā敥")   |
+--------------+
| C480E695A5   |
+--------------+

0xc4 is an invalid string in utf8_general_ci encoding, so the string isn't modified.

SELECT TRIM(0xc4 from "Ā敥");
+-------------------------+
| TRIM(0xc4 from "Ā敥")   |
+-------------------------+
| Ā敥                     |
+-------------------------+

0xc480 is encoding on Ā in utf8_general_ci, so string is trimmed:

SELECT TRIM(0xc480 from "Ā敥");
+---------------------------+
| TRIM(0xc480 from "Ā敥")   |
+---------------------------+
| 敥                        |
+---------------------------+

0xc480e6 is invalid in utf8_general_ci, so the string stays as-is:

SELECT TRIM(0xc480e6 from "Ā敥");
+-----------------------------+
| TRIM(0xc480e6 from "Ā敥")   |
+-----------------------------+
| Ā敥                         |
+-----------------------------+

For characters having 4-byte encoding like emojis when character_server is utf8, explicitly cast string literal to utf8mb4_* collation.

SELECT HEX("😀") as HEX, TRIM(0xf0 from "😀" :> char(20) COLLATE utf8mb4_general_ci) as TRIM;
+----------+------+
| HEX      | TRIM |
+----------+------+
| F09F9880 | 😀   |
+----------+------+

Caution

Implicit Collation

When character_set_server is set to utf8, string literals with characters using 4-byte encoding are implicitly assigned binary collation and processed as a sequence of bytes rather than characters. This implicit conversion to binary collation causes string functions to return unexpected results. To avoid using implicit binary collation, either use explicit type casting or use database columns defined with the utf8mb4 character set.

For more information, refer to Implicit Collation in Special Cases.

Related Topics

Last modified: April 4, 2023

Was this article helpful?

On this page

Was this article helpful?