LTRIM() in SQL

Introduction to LTRIM() in SQL

The Structured Query Language (SQL) comes with the privilege of string functions, which are used chiefly for manipulating the strings and LTRIM() is an SQL inbuilt string function which is used to remove the presence of any leading spaces or empty spaces from the left-hand side in a string and also the LTRIM() function can be used to remove specified characters or substring from the left-hand side of the given string.

Syntax:

LTRIM(string)

In the above syntax, the ‘string’ can be a variable or column or literal string or a character expression from which the empty spaces or characters are trimmed from the left-hand side.

How LTRIM() Function works in SQL?

Let us have a table where we have a column that has some leading empty spaces for the column values which need to be removed. Usually, these kinds of issues with the presence of leading spaces in a string can be handled by the usage of SQL string functions. The LTRIM() function is an inbuilt function that helps in removing these spaces from the left-hand side of the string. Though this function is used to remove all the leading spaces, we can also remove the specific characters from the left-hand side of the given string by using the function as LTRIM(string, substring).

So this function not only removes the leading spaces but also is useful when we want to remove or trim a given substring from the left-hand side of the original string. LTRIM() function only removes or trims the leading spaces in a given string, but any other spaces in the given string are left as it is. For example, if the string consists of two words with space in between them or any trailing spaces are present, the LTRIM() does not remove the trailing spaces or space in between these words.

Examples of LTRIM() in SQL

Let us take a few examples to see how LTRIM() works:

Example #1

The LTRIM() function is used to remove the leading empty spaces from the string “ Hello World”.

SELECT LTRIM(' Hello World') as example;

Output:

We can see from the above that earlier there were few leading spaces present in the string ‘ Hello World’. The use of the LTRIM() string function has removed the extra spaces from the left-hand side of the string as shown by the result.

If in addition to the empty spaces from the left side of the string, there also exists the empty spaces at the right-hand side or in between the string, the spaces in between and the spaces at the right-hand side do not get removed by the usage of LTRIM() function.

Example #2

SELECT LTRIM(' Hello World ') as example;

The above statement will not remove the empty spaces from the right-hand side of the input string or space in between and only removes the empty spaces from the left-hand side of the given string as mentioned below.

Output:

Example #3

If we want to specify the characters to be removed or if we want to remove a substring from the original or given string, then we can also achieve that by using the LTRIM().

SELECT LTRIM('99866', '9') as example;

Output:

In the above example, we can see that as we have specified in the query that all the leading 9s should be removed from the string 99866, the result is 866.

Example #4

In the below statement, the string contains 9s both at the leading as well as trailing positions. But the usage of the LTRIM() function will only remove all the leading 9s but not the 9s from the right-hand side as shown below in the result.

SELECT LTRIM('9986699', '9') as example;

Output:

Example #5

In the below statement, the specified string or the substring 98612 is to be trimmed from the input or given string and it is done with the use of LTRIM() as below.

SELECT LTRIM('99866abcde', '98612') as example;

Output:

Similarly, a set of specified characters also can be trimmed from the leading positions of the input string as below.

Example #6

SELECT LTRIM('abcdefabgh', 'abcd') as example;

Output:

It can be noticed from the above result that the substring ‘abcd’ is trimmed only from the left-hand side of the input string.

Let us take the example of the below table CUSTOMERS.

Let us consider that the table has leading empty spaces for some columns. Also, the column AGE contains ‘&’ added to the age values of the customers.

Example #7

In the above table, the first name i.e. F_NAME column may contain some empty spaces. We can remove the leading empty spaces when it is needed by the usage of the below query.

SELECT LTRIM(F_NAME) as result FROM CUSTOMERS;

The result of the above query will remove the empty spaces from the left-hand side of the column values of F_NAME as shown below.

Output:

Example #8

We can see that the column AGE has ‘&’ added to the values in it. This can be removed by the usage of the LTRIM() function as below.

SELECT LTRIM(AGE, ‘&’) as result FROM CUSTOMERS;

The result of the above query will have all the ‘&’ removed from the leading positions of the values in the column AGE.

Output:

Conclusion

LTRIM() is an inbuilt string function in SQL which helps in removing the leading white spaces from the string and also removes the substring from the left-hand side of the input or given string. It is very important to have a good understanding of this function.

This has been a guide to LTRIM() in SQL. Here we discuss the basic concept, examples and how LTRIM() function works in SQL respectively. You may also have a look at the following articles to learn more –

1. Cheat Sheet MySQL
2. Hive Built-in Functions
3. Oracle String Functions
4. T-SQL String Functions