MySQL LTRIM(), RTRIM() and TRIM()

By /
LTRIM RTRIM And TRIM Functions

In this tutorial, we will learn about the MySQL LTRIM(), RTRIM() and TRIM() functions. Data in tables might not always be clean. By not being clean, I mean the values may have excessive spaces or other jargon or no value at all. Suppose you have a table containing Country names.

When you take a look at the table, you see that the country name values have extra whitespaces at the beginning of the string or the end of the string, or worse yet, both ends of the strings. For instance, your table has a value “ India “.

These extra whitespaces are considered as values and stored in the table even though it may not be appropriate for your use case. For this purpose, MySQL provides us with three trimming functions – to trim whitespaces and other unnecessary characters from our string.

  • LTRIM() is used to remove the leading spaces (spaces on the left side) from a string.
  • RTRIM() is used to remove the trailing spaces (spaces on the right side) from a string.
  • TRIM() is used to remove the leading and the trailing spaces from a string.

Syntax of MySQL LTRIM()

LTRIM(string);Code language: SQF (sqf)

Where ‘string’ is the string whose leading spaces are to be trimmed.

Syntax of MySQL RTRIM()

RTRIM(string);Code language: SQF (sqf)

Where ‘string’ is the string whose trailing spaces are to be trimmed.

Syntax of MySQL TRIM()

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM ] string);Code language: SQL (Structured Query Language) (sql)

Where:

  • ‘string’ is the string whose leading and trailing spaces are to be trimmed,
  • ‘remstr’ is an optional parameter that is used to specify a character that needs to be trimmed out of the string.
  • The parameters – BOTH, LEADING and TRAILING are used to specify which side of the string is to be trimmed. 
  • BOTH is used to trim both the leading and trailing ends of the string.
  • LEADING is used to specify that TRIM() should only trim the leading end of the string. Specifying this parameter without ‘remstr’ makes TRIM() equal to LTRIM().
  • TRAILING is used to specify that TRIM() should only trim the trailing end of the string. Specifying this parameter without ‘remstr’ makes TRIM() equal to RTRIM().

Examples of MySQL LTRIM()

Let us take a look at a basic example of LTRIM() using the SELECT statement.

SELECT LTRIM('  Hello World');Code language: SQL (Structured Query Language) (sql)

The function trims the whitespaces before the ‘Hello World’ string and returns the output as follows,

Ltrim Basic Example

MySQL LTRIM() With Tables

Consider the below ‘Country’ table. Note that we will be using this table for our examples of RTRIM() and TRIM() as well.

Country Table Trim
Country Table

As you can see, the Name column seems to have a lot of leading and trailing spaces. Let us display the table but with the leading spaces in the Name column removed. We do this using the below query,

SELECT ID, LTRIM(Name) FROM Country;Code language: SQL (Structured Query Language) (sql)

And the output we get is,

Ltrim Table Example

Examples of MySQL RTRIM()

Let us take a look at a basic example of RTRIM() using the SELECT statement. We will use an alias to make our output readable.

SELECT RTRIM(' Hello World ') AS RightTrimmed;Code language: SQL (Structured Query Language) (sql)

The function trims the whitespaces after the ‘Hello World’ string and returns the output as follows,

Rtrim Basic Example

MySQL RTRIM() With Tables

Let us consider the ‘Country’ table we saw earlier. That one still needs a lot of trimming. Let us write a query that displays the following:

  • ID column,
  • Name column,
  • Length of the values in the Name column,
  • Name column with the trailing spaces removed,
  • Length of the values in the Name column after the trailing spaces are removed.

We will display this using the SELECT statement. To find the length of the values, we will use the LENGTH() function. The query is,

SELECT ID, Name, LENGTH(Name), RTRIM(Name) AS RightTrimmedString, 
LENGTH(RTRIM(Name)) AS LengthOfRightTrimmedString FROM Country;Code language: SQL (Structured Query Language) (sql)

And the output we get is,

Rtrim Table Example

Examples of TRIM()

Let us now look at a few examples of the MySQL TRIM() function. First, let us start with a basic example. Consider the below query,

SELECT TRIM(' Hello World ') AS TrimmedString;Code language: SQL (Structured Query Language) (sql)

TRIM() will remove the leading and trailing spaces in the string and return the output as follows:

Trim Basic Example

Using BOTH in TRIM()

Consider the below examples.

SELECT TRIM(BOTH 's' FROM 'swans') AS TrimmedString; 
SELECT TRIM(BOTH FROM ' Hello World ') AS TrimmedString;Code language: SQL (Structured Query Language) (sql)

We use BOTH to tell TRIM() that it should trim from both ends of the string. BOTH is usually mentioned if we want to mention a character that needs to be removed from the leading or trailing ends of the string and shown in the first query above. If we do not mention a character, then TRIM() acts just like the basic example we saw earlier.

The output is,

Trim Both Example

Using LEADING in TRIM()

Consider the below examples.

SELECT TRIM(LEADING FROM ' Hello World ') AS TrimmedString; 
SELECT TRIM(LEADING 'H' FROM 'Hello World') AS TrimmedString;Code language: SQL (Structured Query Language) (sql)

We use LEADING to tell TRIM() that it should trim only from the leading end of the string. LEADING is usually mentioned if we want to mention a character that needs to be removed from the leading end of the string and shown in the second query above. However, if we do not mention a character, then TRIM() acts just like the LTRIM() function as you can see in the first example.

The output is,

Trim Leading Example

Using TRAILING in TRIM()

Consider the below examples.

SELECT TRIM(TRAILING FROM ' Hello World ') AS TrimmedString; 
SELECT TRIM(TRAILING 'd' FROM 'Hello World') AS TrimmedString;Code language: SQL (Structured Query Language) (sql)

We use TRAILING to tell TRIM() that it should trim only from the trailing end of the string. TRAILING is usually mentioned if we want to mention a character that needs to be removed from the trailing end of the string and shown in the second query above. However, if we do not mention a character, then TRIM() acts just like the RTRIM() function as you can see in the first example.

The output is,

Trim Trailing Example

TRIM() with Tables

Let us consider the earlier ‘Country’ table. How about we update the Name column by removing the leading and trailing spaces in its values and then displaying the table? We will use the UPDATE statement.

UPDATE Country SET Name=TRIM(Name); 
SELECT * FROM Country;Code language: SQL (Structured Query Language) (sql)

And the output is,

Trim Table Example

Conclusion

LTRIM(), RTRIM() and TRIM() find a lot of applications with real-world data and tables. The data in the tables is not clean all the time and for this cleaning process, you will have to use these trimming functions. You can read more about them on the links below.

References