Feeds:
Posts
Comments

Posts Tagged ‘LTRIM() function’

SQL SERVER 2022 – Error – Fix – Invalid length parameter passed to the LTRIM() function

Posted in Errors, SQL SERVER, tagged , , , on March 29, 2023| Leave a Comment »

In SQL Server 2022, an enhancement came in LTRIM() function, which was a long awaited enhancement. This enhancement helps us to remove first character from the string. I have written an article about it in detail.

Recently, I was using LTRIM() function and came across an error as mentioned below.

Error:

Invalid length parameter passed to the ltrim function.

Sample:

 
USE tempdb
GO

CREATE TABLE #Employees
(
EmployeeID   INT IDENTITY(1,1),
EmployeeName VARCHAR(250),
EmailAddress VARCHAR(MAX)
);
GO

INSERT INTO #Employees
VALUES ('Alex','alex114@gmail.com;')
     , ('Sarina','sarina152@gmail.com;')
     , ('Sophia','sophiaa123@gmail.com;');
GO

SELECT * FROM #Employees;
GO

Example:

In the given below example, I have written a simple script using LTRIM() function and I declared separator as variable and kept its data type & length as VARCHAR(MAX). This script generates an error as shown below.

USE tempdb
GO

DECLARE @separator VARCHAR(MAX);
SET @separator = ';';

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , LTRIM(EmailAddress, @separator) AS EmailAfterLTrim
FROM #Employees;
GO

Solution:

The solution to this problem is that while declaring datatype and length of a separator variable, we should never use MAX as its length since it does not support. We should always give length in numbers as shown below.

USE tempdb
GO

DECLARE @separator VARCHAR(1);
SET @separator = ';';

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , LTRIM(EmailAddress, @separator) AS EmailAfterLTrim
FROM #Employees;
GO

Conclusion:

Whenever you use LTRIM() function by using a separator variable, you should always define the length of the variable. Please don’t use MAX as a length to avoid such error.

Read Full Post »

SQL Server 2022 – TSQL Enhancement – LTRIM() Function

Posted in Functions, SQL SERVER, tagged , , , on November 8, 2022| 1 Comment »

We all have been using LTRIM() function for ages, it helps us to removes the unwanted space(s) character char(32) from the start of any column(s) \ expression(s) in earlier version of SQL Server.

In SQL Server 2022, an enhancement came in LTRIM() function, which was a long awaited functionality. This enhancement will allow us to remove any specific character(s) from the left sides along with space(s) character char(32).

Let me show you in the below example, how we were using LTRIM() functions to remove space(s) character char(32) ONLY in the earlier version of SQL Server.

Example 1:

DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = '     KenSanchez@gmail.com';
SELECT  @EMAIL   AS [OriginalEmail]
, LTRIM(@EMAIL)  AS [LTrimEmail];
GO
--OUTPUT

Enhancement:

Now, let me demonstrate this enhancement by giving few examples but before demonstrating this enhancement, your database compatibility level MUST be 160 or higher. Given below is the query to change your database compatibility level.

 ALTER DATABASE AdventureWorks SET COMPATIBILITY_LEVEL = 160

Example 2:

In this example, I will show you how to pass the newly introduced parameter in LTRIM() function. This parameter removes spaces(s) / character(s) from the LEFT side of the column(s) / expression(s) which will be defined in single quotes after LTRIM() as shown below.

DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = '     ;KenSanchez@gmail.com';
SELECT  @EMAIL       AS [OriginalEmail]
, LTRIM(@EMAIL,'; ')  AS [LTrimEmail];
GO
--OUTPUT

Example 3:

In this example, I will demonstrate that by default LTRIM() function takes spaces(s) as parameter, if you do not pass any parameter as shown in below example: 

DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = '     KenSanchez@gmail.com';
SELECT  @EMAIL   AS [OriginalEmail]
, LTRIM(@EMAIL)  AS [LTrimEmail]
, LTRIM(@EMAIL,' ') AS [LTrimEmailWithParameter];
GO
--OUTPUT

Conclusion:

I found the new enhancement of LTRIM() function very useful, earlier we used multiple functions to find and remove any character(s) from the LEFT side of the column(s) \ expression(s) but now we can easily achieve with the help of LTRIM() function. Do let me know if you use this function and how you find it.

Read Full Post »