Understanding the SQL LTRIM() Function By Practical Example

SQL LTRIM Function Summary: In this article, you will learn What is SQL LTRIM() function and How to use the SQL LTRIM Function to remove leading blanks from a string.
Furthermore, we’ll review the new SQL TRIM function in SQL Server 2017 onwards yet as providing few information on strings functions that pre-date it such as LTRIM AND RTRIM.

SQL Developers sometimes face problem with spaceS at the start and/or end of a character string. Furthermore, we have a tendency to might have to trim leading and trailing characters from a character string. We might need to do string manipulations with SQL functions yet.

Suppose we would like to remove spaces from the trailing finish of a string, we might to use SQL LTRIM and RTRIM functions until SQL Server 2016.

In SQL Server 2017, we have a tendency to get new built-in function to trim each leading and trailing characters together with a single function.

SQL TRIM function provides additional functionality of removing characters from the desired string. We will still use RTRIM and LTRIM function with SQL Server 2017 yet. Let’s explore these functions with prcatical examples.

Definition and Usage of SQL LTRIM function

SQL LTRIM Function removes characters from starting (beginning from the left side) in the specific string. with in following query, we ‘ve white space before and after the string. if you want to remove the space from the left side of the string then using the SQL LTRIM function.

We use the SQL Server DATALENGTH() function to calculate data length in bytes before and after using SQL LTRIM function

Let’s see, SQL LTRIM function example and explore how to use the SQL Server LTRIM function in SQL Server (Transact-SQL).


Use of SQL Server LTRIM Function


SQL LTRIM

SQL Server RTRIM function

SQL RTRIM function removes characters from the end ((Starting from the right side) of the desired string. Let’s execute the given below following query to see at the effect of RTRIM function.


SQL RTRIM Function

TRIM function in SQL Server 2017

Before SQL Server 2017, if we need to remove both Leading and Training spaces, we’d like to use both LTRIM and RTRIM function. In the given below following query, we used both LTRIM and RTIM functions with a string variable.

In the screenshot, we can see it removes both leading and trailing space from the string.

Data String size in Bytes for Original String: 24
Data String size in Bytes after SQL LTRIM: 11

TRIM Function
Therefore, we need to use 2 functions to get rid of the areas from the string. we will use one instance of a TRIM perform in SQL Server 2017 forrader to trim each leading and trailing characters.

The Syntax of the SQL TRIM Function

  • TrimCharacter: we are able to specify the character to remove from the string. furthermore, by default, the SQL Server removes spaces from both sides of a string
  • String: We need to specify the string that we would like to trim

Let’s explore SQL Server TRIM function with examples.

TRIM Leading and Training spaces:

By default, SQL Server Trim function removes the spaces from the both sides of a string. In the given below following query, Therefore, defined a string with space on both sides of the string.
Let’s execute the query:

We didn’t specified any characters to remove in SQL Server TRIM function. In output result, SQL TRIM function removes the both leading and trailing space.
You can see that the difference in string length before and after the SQL TRIM function.

  • Data String size in Bytes for the Original String: 25
  • Data String size in Bytes after SQL TRIM: 12


TRIM Function

Note: If SQL Trim Function not working in SQL Server

Sometimes, when you use the SQL TRIM function in SQL server, its show Error that "TRIM is not a recognized built-in function name". you will be face the problem such as trying to trim the empty space at the end of string but the value is not getting trimmed.
Answer:
As you can see in the result of the query, TRIM() do the same as RTRIM(LTRIM()).
you can use RTRIM(LTRIM()) in the place of TRIM() function, yo get the same result.
I will show you how to get the same result, as you can see the just above query, i have used the TRIM function, and getting the output of the query.

  • Data String size in Bytes for the Original String: 25
  • Data String size in Bytes after SQL TRIM: 12

Therefore, i will replace the TRIM() function to RTRIM(LTRIM()) then you can write the above query as given below.


TRIM Function

Conclusion

you have learned SQL LTRIM() function, SQL TRIM() Function, SQL RTRIM() Function and How to use the SQL LTRIM Function to remove leading blanks from a string. I hope you will enjoy! if have any query, contact me, I have try to resolve the problem.