TRIM
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
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: May 29, 2025