MySQL/MariaDB How to remove spaces in a string - TRIM function

MySQL/MariaDB How to remove spaces in a string - TRIM function

Colleagues hello to all.

In today's article, we'll talk about how you can remove spaces at the beginning and end of a string when executing queries against a MySQL and MariaDB database.

When a user fills out a form on your site and then submits this form, he may accidentally indicate extra spaces somewhere, or do it on purpose. The task of the developer is to keep track of such things at the level of the programming language, to remove these spaces in the lines before sending the data to the database. But it may happen that the developer forgets or does not want to spend extra time checking such trifles and skips data that has spaces at the beginning or end of the line, and then problems may arise at the stage of inserting this data into the database.

If you or a developer do not know how to implement removal of extra spaces in strings at the programming language level, then three functions that are in MySQL or MariaDB database can come to our aid. The functions are called RTRIM(), TRIM(), and LTRIM(), and they are great for this kind of task. We will consider them all in turn.

 

RTRIM() function.

Function syntax:

RTRIM(string);

The function takes a string as a parameter and returns a string without spaces at the end of the string.

 

The RTRIM() function is used to remove all spaces at the end of a string.

sql> SELECT RTRIM('Hellow World      ') as rtrim_function;

mysql mariadb function trim

As you can see in the example, I added a lot of spaces to the end of the string, and as a result, the RTRIM() function removed these spaces for us.


 

LTRIM() function.

Function syntax:

LTRIM(string);

The function takes a string as a parameter and returns a string without spaces at the beginning of the string.

 

The LTRIM() function is used to remove all spaces at the beginning of a string.

sql> SELECT LTRIM('      Hello World') as ltrim_function;

mysql mariadb function trim

As you can see in this example, I added a lot of spaces to the beginning of the string, and as a result the LTRIM() function successfully removed these spaces for us.


 

TRIM() function.

Function syntax:

TRIM([{BOTH|LEADING|TRAILING} [removed_string]] FROM string);

  • LEADING - Removes a character from the beginning of a line.
  • BOTH - Removes a character from the beginning and end of a string.
  • TRAILING - Removes a character from the end of a string.

BOTH is the default setting. If no parameters are passed to the function, then the default function will remove only spaces from the beginning and end of the string.

This function is universal, it can remove not only problems from the string, but also characters that we do not need.

 

In the first example, we want the function to remove the extra spaces at the beginning and end of the string for us.

sql> SELECT TRIM('           Hellow World              ') as trim_function;

mysql mariadb function trim

The result is excellent! No spaces!

 

In the following example, I want the function to remove the H character from the beginning of the string Hellow World.

sql> SELECT TRIM(LEADING 'H' FROM 'Hellow World');

mysql mariadb function trim

As you can see, as a result, the symbol H is gone.

 

In the next example, I want the Hellow function to remove the w character from the end of the string.

sql> SELECT TRIM(TRAILING 'w' FROM 'Hellow');

mysql mariadb function trim


 

Thank you all, I hope that my article helped you in some way.

 

SIMILAR ARTICLES

MySQL - Test Data Generation
MySQL - Test Data Generation
MYSQL
Read more
MySQL / MariaDB How to get the id of the last inserted record in a table - LAST_INSERT_ID
MySQL / MariaDB How to get the id of the last inserted record in a table - LAST_INSERT_ID
MYSQL
Read more
MySQL/MariaDB How to convert character case - UPPER and LOWER functions
MySQL/MariaDB How to convert character case - UPPER and LOWER functions
MYSQL
Read more