SQL Server LTRIM Function

In this SQL Server tutorial, you will learn about the SQL Server LTRIM function to remove the leading spaces from the character expression.

Where you will understand the use of the LTRIM() function with literal string, and variable to remove the character from the string. Also, you will learn how to remove the specified character from the string.

Finally, you will use the LTRIM() function with the table column to truncate the spaces.

Table of Contents

The LTRIM() function in SQL Server removes or truncates the space character ( which is CHAR(32 )) or other mentioned character from the beginning of the string. In other words, to remove the leading spaces or any character from the left side of the string, use the LTRIM() function.

The syntax is given below.

LTRIM(str_exp, specified_char)

Where,

LTRIM(): It is the function that removes the spaces or other specified characters from the string.
str_exp: It is a string expression, that can be constant, column, literal string, or variable. Provide any datatype except ntext, text and image. It accepts the value that can be implicitly converted into varchar.
specified_char: It is the character that you want to remove from the string expression. It is optional and by default LTRIM() function removes leading spaces from the string expression.

If you input the NULL value to the LTRIM() function, it returns the NULL. For other datatypes, LTRIM() returns the character expression.

Now let’s see with different examples how to use the LTRIM() function in SQL Server.

SQL Server LTRIM Function: Remove Leading Spaces

The leading spaces are white spaces such as spaces, tabs, or non-visible characters, and these spaces appear on the left side or beginning of the string. In general, these spaces are characters.

For example, look at this literal string ‘ Australia’, it contains leading spaces. So here you will understand how to remove this kind of space from the beginning of the strings.

For that use the below query.

SELECT LTRIM('  United Satates of America') AS LeftTrimLeadinSpaces;

SQL Server LTRIM Function Remove Leading Spaces

After the execution of the above query, it removes the leading spaces from the string ‘ United States of America’ that you can see in the above picture.

This is how you can remove the leading spaces from the string using the SQL Server LTRIM function.

Next, let’s see how to remove the specified character from the string.

SQL Server LTRIM Function: Remove Specified Character

Using the second argument of the LTRIM() function you can specify the character that you want to remove or truncate from the beginning of the given string.

For example, look at this string ‘–james@gmail.com’, now you want to remove the hyphen (-) from the email address. For that use the below query.

SELECT LTRIM('--james@gmail.com','-') AS LeftTrimSpecificChar;

SQL Server LTRIM Function Remove Specified Character

From the above picture, look at the output of the query, which contains the string ‘james@gmail.com’. So here LTRIM() function removes the hyphen (-) character from the beginning of the string ‘–james@gmail.com’ that you can see in the above output.

Let’s take one more example with a different character such as &, for example, you have the string ‘&&&Warner Price’, and this string contains the ampersand (&) character at the beginning of the name.

To remove the & character from the left side of the string, use the below query.

SELECT LTRIM('&&&Warner Price','&') AS LeftTrimSpecificChar;

SQL Server LTRIM Function Remove Specified Ampersand Character

When you run the above query it returns the result which is the string ‘Warner Price’ without the ampersand (&) character that you wanted to remove from the string ‘&&&Warner Price’.

In the query part LTRIM(‘&&&Warner Price’,’&’), the first value is string ‘‘&&&Warner Price’ and the second value is the ampersand (&) character that wants to remove from the string.

So using the SQL Server LTRIM function, you can remove any specified character from the beginning of the given string.

Now next, let’s see how to remove characters from the beginning of the variable containing the string value.

SQL Server LTRIM Function: Remove Space from Variable

The variable is a named storage that holds the data temporarily. So here you will create a variable that holds the string value with leading spaces and remove that space using the LTRIM() function of SQL Server.

So first create a variable named str and assign the string value ‘ I live in Canada’ using the below query.

DECLARE @str VARCHAR(100) = '     I live in Canada';

After declaring the variable, use the below query to remove the spaces from this variable.

SELECT @str As ActualString, LTRIM(@str) AS LtrimFromVariable;

SQL Server LTRIM Function Remove Space from Variable

In the above picture, look at the output of the query which is indicated using the green arrow. The LTRIM() function removed the leading spaces from the variable @str which contains the string ‘ I live in Canada’. As a result, the string became ‘I live in Canada’.

Also, you can remove any specified character from the variable as you have done in the previous section. Here only difference is that the string is stored within a variable.

Now let’s see how to use the LTRIM() function with the table column.

SQL Server LTRIM Function: Remove Space from Table

You can also remove the leading spaces from the table column values using the LTRIM() function.

For example, you have a Product table with columns ProductID and ProductName. In the ProductName column, some of the product name contains leading spaces which is shown below.

SQL Server LTRIM Function Remove Space from Product Table

Now you have to retrieve the product names without leading spaces. For that use the below query.

SELECT LTRIM(ProductName) AS WithoutLeadingSpace
FROM Product;

SQL Server LTRIM Function Remove Space Product Table

When you execute the above query, it removes spaces from the beginning of each product name in the ProductName column of the Product table, which you can see in the above output.

Well, this is you can use the LTRIM() function to remove the leading spaces or specified characters from the table column in SQL Server. So now you know about how SQL Server LTRIM function works.

Conclusion

In this SQL Server tutorial, you learn how to remove the leading spaces or specified characters from the string, also from the variable in SQL Server. Then learned how to apply the LTRIM() function on the table column to remove the spaces from the beginning of the values.