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.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.\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_collation_ setting.
SELECT @@collation_server;
+--------------------+
|@@collation_server |
+--------------------+
| utf8_general_ci |
+--------------------+SELECT HEX("Ā敥");
+--------------+
| HEX("Ā敥") |
+--------------+
| C480E695A5 |
+--------------+0xc4 is an invalid string in utf8_
SELECT TRIM(0xc4 from "Ā敥");
+-------------------------+
| TRIM(0xc4 from "Ā敥") |
+-------------------------+
| Ā敥 |
+-------------------------+0xc480 is encoding on Ā in utf8_
SELECT TRIM(0xc480 from "Ā敥");
+---------------------------+
| TRIM(0xc480 from "Ā敥") |
+---------------------------+
| 敥 |
+---------------------------+0xc480e6 is invalid in utf8_
SELECT TRIM(0xc480e6 from "Ā敥");
+-----------------------------+
| TRIM(0xc480e6 from "Ā敥") |
+-----------------------------+
| Ā敥 |
+-----------------------------+For characters having 4-byte encoding like emojis when character_
SELECT HEX("😀") as HEX, TRIM(0xf0 from "😀" :> char(20) COLLATE utf8mb4_general_ci) as TRIM;
+----------+------+
| HEX | TRIM |
+----------+------+
| F09F9880 | 😀 |
+----------+------+Caution
Implicit Collation
When character_ 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.utf8mb4 character set.
For more information, refer to Implicit Collation in Special Cases.
Related Topics
Last modified: April 4, 2023