Remove Spaces in SQL

One of the most common tasks in SQL and other programming languages is manipulating and working with string data. This might include the string concatenation, upper or lower casing conversion, string trimming, and many more.

One of the most common string manipulation tasks in SQL is trimming or removing the whitespace characters from a given input string.

In this tutorial, we will learn about the LTRIM() function which plays a crucial role in string trimming.

SQL LTRIM

In SQL, the LTRIM() function stands for left trim. The function allows us to remove any and/or leading (leftmost) characters from a given string value.

This function is particularly useful when dealing with data that may contain unnecessary whitespace at the beginning of strings which allows us to cleanup the values from the database.

Syntax:
The syntax for the LTRIM function in SQL may vary slightly depending on the database engine. In MySQL, the syntax is as follows:

LTRIM(string_to_trim)

The “string_to_trim” specifies the input string from which we wish to remove any leading whitespace characters.

SQL LTRIM Example Usage (MySQL)

Let us look at some practical examples on how to use the LTRIM function. We start with a basic usage and then proceed to cover some more advanced examples.

Sample Data
Before diving into the queries, consider an example table that contains the employee data as shown in the following:

Suppose we wish to retrieve the “job_title” from the table where the leading whitespace characters are removed. We can use the LTRIM function as demonstrated in the following:

SELECT LTRIM(job_title) AS cleaned_job_title FROM emp;

Output:

Example: Trimming Specific Characters
By default, the LTRIM function removes the space characters from the input string. However, we can specify the specific characters that we wish to remove from the input string.

For example, to remove all occurrence of the tab character, we can use the “\t” value as shown in the following example:

SELECT LTRIM('\t' FROM last_name) AS trimmed_last_name FROM emp;

This should remove all tab characters from the strings in the specified column.

NOTE: You can specify any supported character.

SQL RTRIM

In SQL, the RTRIM() function stands for right trim. The function allows us to remove any and/or trailing (rightmost) characters from a given string value.

This function is particularly useful when dealing with data that may contain unnecessary whitespace at the end of strings which allows us to cleanup the values from the database.

Syntax:
The syntax for the RTRIM function in SQL may vary slightly depending on the database engine. In MySQL, the syntax is as follows:

RTRIM(string_to_trim)

The “string_to_trim” specifies the input string from which we wish to remove any leading whitespace characters.

SQL RTRIM Example Usage (MySQL)

Let us look at some practical examples on how to use the RTRIM function. We start with a basic usage and then proceed to cover some more advanced examples.

Sample Data
Before diving into the queries, consider an example table that contains the employee data as shown in the following:

Suppose we wish to retrieve the “job_title” from the table where the leading whitespace characters are removed. We can use the RTRIM function as demonstrated in the following:

SELECT RTRIM(job_title) AS cleaned_job_title FROM emp;

Output:

Example: Trimming Specific Characters
By default, the RTRIM function removes the space characters from the input string. However, we can specify the specific characters that we wish to remove from the input string.

For example, to remove all occurrence of the tab character, we can use the “\t” value as shown in the following example:

SELECT RTRIM('\t' FROM last_name) AS trimmed_last_name FROM emp;

This should remove all tab characters from the strings in the specified column.

NOTE: You can specify any supported character.

SQL TRIM

We also have the advantage of both worlds. The TRIM() functions remove any leading and trailing whitespace characters from a string.

An example is as follows:

SELECT TRIM(' Hello World ') AS TrimmedString;

This should remove the space characters from the string.

Conclusion

We discussed how to use the trim functions to remove the whitespace characters from the beginning, end, or both ends of a string.