TRIM
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 | 😀 |
+----------+------+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.