TRIM() string function in MySQL – with examples.

In this post, I’ll cover examples of the MySQL TRIM() function. TRIM() removes specific characters – or spaces – from a given string, at either: the beginning, ending, or potentially in both locations depending on several factors. With an optional keyword argument that controls which character(s) – if any – are removed, TRIM() can be tricky so let’s gain understanding with several easy-to-digest examples…

trimmers trimming hedge bushes
Photo by Peter Beukema on Unsplash

OS, Software, and DB used:

  • OpenSuse Leap 15.1
  • MySQL 8.0.20

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

TRIM() accepts zero or one of the below keywords along with character(s) to remove, and a target string in which to remove them:

  • LEADING
  • TRAILING
  • BOTH

The given keyword name is relatively self-explanatory and specifies a location within the target string in which characters are removed. Should none of the 3 available location-specific keyword arguments be specified – which is perfectly valid as they are optional – TRIM() works as if the BOTH keyword had been given.

The LEADING keyword causes the target character(s) to be removed from the beginning of the given string:

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM(LEADING 'l' FROM 'level') AS trimming;
+---------+
| trimming |
+---------+
| evel    |
+---------+
1 row in set (0.0007 sec)

Whereas the opposite is the case for TRAILING, removing characters from the end of the string:

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM(TRAILING 'l' FROM 'level') AS trimming;
+----------+
| trimming |
+----------+
| leve     |
+----------+
1 row in set (0.0007 sec)

And, as you would likely surmise, BOTH indicates the beginning and ending locations for modification:

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM(BOTH 'l' FROM 'level') AS trimming;
+----------+
| trimming |
+----------+
| eve      |
+----------+
1 row in set (0.0006 sec)

In the next query, since the word ‘level’ begins and ends with an ‘l’, TRIM() removes both of the ‘l’ characters at each end, operating as if the BOTH keyword was included. Again, the default action if no location is given:

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM('l' FROM 'level') AS trimming;
+----------+
| trimming |
+----------+
| eve      |
+----------+
1 row in set (0.0006 sec)

Same behavior for the word ‘lol’:

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM('l' FROM 'lol') AS trimming;
+----------+
| trimming |
+----------+
| o        |
+----------+
1 row in set (0.0010 sec)

However, the target remove string character(s) case is significant (case-sensitive), as shown in the following 2 queries:

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM('l' FROM 'LOL') AS trimming;
+----------+
| trimming |
+----------+
| LOL      |
+----------+
1 row in set (0.0007 sec)

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM('L' FROM 'lol') AS trimming;
+----------+
| trimming |
+----------+
| lol      |
+----------+
1 row in set (0.0010 sec)

In the previous queries, no characters were removed since the case of neither matched any found characters in the strings ‘LOL’ and ‘lol’ respectively.

Can TRIM() remove characters located in other segments besides the beginning or ending positions of the string?

No, TRIM() does not operate in that manner, as shown in the following query:

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM('r' FROM 'umbrella') AS trimming;
+----------+
| trimming |
+----------+
| umbrella |
+----------+
1 row in set (0.0005 sec)

For these 2 queries, if neither LEADING nor TRAILING is specified, and should the target letter – ‘L’ in this example – be located in either the beginning or ending position of the string, TRIM() removes it:

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM('L' FROM 'Lol') AS trimming;
+----------+
| trimming |
+----------+
| ol       |
+----------+
1 row in set (0.0007 sec)

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM('L' FROM 'loL') AS trimming;
+----------+
| trimming |
+----------+
| lo       |
+----------+
1 row in set (0.0006 sec)

Again, default behavior from TRIM().

TRIM() also accepts double characters as the target removal portion(s), to a certain extent. View these next example queries for better understanding:

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM(TRAILING 'gg' FROM 'egg') AS trimming;
+----------+
| trimming |
+----------+
| e        |
+----------+
1 row in set (0.0009 sec)

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM(TRAILING 'ee' FROM 'bee') AS trimming;
+----------+
| trimming |
+----------+
| b        |
+----------+
1 row in set (0.0006 sec)

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM('ee' FROM 'bee') AS trimming;
+----------+
| trimming |
+----------+
| b        |
+----------+
1 row in set (0.0006 sec)

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM('Aa' FROM 'Aabba') AS trimming;
+----------+
| trimming |
+----------+
| bba      |
+----------+
1 row in set (0.0006 sec)

However, location is key to how TRIM() removes characters. Matching characters located anywhere other than at the beginning or end of the target string are not removed:

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM('ll' FROM 'parallel') AS trimming;
+----------+
| trimming |
+----------+
| parallel |
+----------+
1 row in set (0.0006 sec)

The removal character string is optional. If not included in the call to TRIM(), spaces (if any are present) are removed:

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM('   Josh') AS trimming;
+----------+
| trimming |
+----------+
| Josh     |
+----------+
1 row in set (0.0007 sec)

Much of TRIM()‘s behavior depends on what words and target string for removal are given, along with any location keyword arguments.

TRIM() can also easily remove multiple characters from target words as shown in these next examples:

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM('bee' FROM 'bumblebee') AS trimming;
+----------+
| trimming |
+----------+
| bumble   |
+----------+
1 row in set (0.0008 sec)

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM(BOTH 'bee' FROM 'bumblebee') AS trimming;
+----------+
| trimming |
+----------+
| bumble   |
+----------+
1 row in set (0.0381 sec)

1
2
3
4
5
6
7
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT TRIM(TRAILING 'bee' FROM 'bumblebee') AS trimming;
+----------+
| trimming |
+----------+
| bumble   |
+----------+
1 row in set (0.0007 sec)

Visit the official documentation on TRIM() for more information.

Although I have not had to use TRIM() that much in the wild, I wanted to post this piece in case other readers and MySQL/PHP developers are interested in learning more about it.

Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.