Feeds:
Posts
Comments

Posts Tagged ‘TRIM’

SQL SERVER 2022 – Error – Fix – Invalid length parameter passed to the TRIM() Function

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

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

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

Error:

Invalid length parameter passed to the trim 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 TRIM() function and I declared separator as variable and kept its data type & length as VARCHAR(MAX). This script generates an error as shown below.

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

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , TRIM(@separator FROM EmailAddress) AS EmailAfterTrim
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.

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

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , TRIM(@separator FROM EmailAddress) AS EmailAfterTrim
FROM #Employees;
GO

Conclusion:

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

Read Full Post »

SQL SERVER 2022 – Multiple ways to remove the last character in a string

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

Removing the last character in a string has never been easier before SQL Server 2022 version. I had written a detailed article back in 2013 regarding this issue, where I used, STUFF(), LEN() functions etc.

In SQL Server 2022, a new enhancement came in TRIM() & RTRIM() function, where we can define any characters to be removed from the String. I had written a detailed article about it earlier. By using above mentioned functions, we can simply remove the last character from the string.

Compatibility Level:

Database compatibility level MUST be 160 or higher to use New Approaches. Given below is the query to change your database compatibility level.


ALTER DATABASE AdventureWorks
SET COMPATIBILITY_LEVEL = 160

Let me demonstrate, how to remove last character from string in the following examples.

Example 1:

  • Old Approach:

In the old approach, we have to use multiple functions (STUFF(), LEN()) in order to remove the last character from string as shown below.

DECLARE @String as VARCHAR(50);
SET @String = 'KenSanchez@gmail.com;';
 
SELECT @String AS String
     , STUFF(@String,LEN(@String), 1, '') AS LastCharacterRemoved;
GO
--OUTPUT

  • New Approach:
  • Using TRIM() function :

In the new approach, its quite simple, we can remove the last character from string by using TRIM() function as shown below.

DECLARE @String as VARCHAR(50);
SET @String = 'KenSanchez@gmail.com;';
 
SELECT @String AS String
     , TRIM(TRAILING';' FROM @String) AS LastCharacterRemoved;
GO
--OUTPUT

  • Using RTRIM() function:

In the new approach, we can remove the last character from string by using RTRIM() function as shown below.

DECLARE @String as VARCHAR(50);
SET @String = 'KenSanchez@gmail.com;';
 
SELECT @String AS String
     , RTRIM(@String,';') AS LastCharacterRemoved;
GO
--OUTPUT

Example 2:

  • Old Approach:

In the old approach, we had to use multiple functions (STUFF(), LEN()) in order to remove the last character from string as shown below.

USE Northwind
GO

SELECT EmployeeID
     , FirstName
     , LastName
     , Title
     , TitleOfCourtesy
     , STUFF(TitleOfCourtesy 
     , LEN(TitleOfCourtesy), 1, '') AS LastCharacterRemoved
FROM Employees;
GO
--OUTPUT

  • New Approach:
  • Using TRIM() function:

In the new approach, its quite simple, we can remove the last character from string by using TRIM() function as shown below.

USE Northwind
GO

SELECT EmployeeID
     , FirstName
     , LastName
     , Title
     , TitleOfCourtesy 
     , TRIM(TRAILING '.' FROM TitleOfCourtesy) AS LastCharacterRemoved 
FROM Employees;
GO
--OUTPUT

  • Using RTRIM() function:

In the new approach, we can remove the last character from string by using RTRIM() function as shown below.

USE Northwind
GO

SELECT EmployeeID
	 , FirstName
	 , LastName
	 , Title
	 , TitleOfCourtesy 
	 , RTRIM(TitleOfCourtesy,'.') AS LastCharacterRemoved
FROM Employees;
GO
--OUTPUT

Conclusion:

In SQL Server 2022 or above, removing the last character from the string is quite easy, since there are multiple functions available to achieve it as shown above. Do let me know which approach are you using and how did you find it.

Read Full Post »

SQL Server 2022 – TSQL Enhancements – TRIM() Function

Posted in Functions, SQL SERVER, tagged , , , , , on November 7, 2022| 2 Comments »

As we all know, TRIM() function removes the extra space(s) character char(32) from the start & end of any column(s) \ expression(s) in earlier version of SQL Server.

In SQL Server 2022, an enhancement is available in TRIM() function, which was a long awaiting functionality. This enhancement will allow us to remove any specific character(s) from one of the sides (left, right) or both sides of the column(s) \ expression(s) along with space(s) character char(32).

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

DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = '     KenSanchez@gmail.com      ';

SELECT  @EMAIL   AS [OriginalEmail]
, TRIM (@EMAIL)  AS [TrimEmail];
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

The enhancement in TRIM() function provides three new options as shown below.

  • Leading
  • Trailing
  • Both

Let me explain these options in detail.

  • Leading

I will demonstrate to you how to use LEADING option in TRIM() function. This option removes space(s) / character(s) from the LEFT side of the column(s) / expression(s) which will be defined in single quotes after LEADING option, as shown in given below example. 


DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = '  ;KenSanchez@gmail.com';

SELECT @EMAIL  AS [OriginalEmail]
     , TRIM(LEADING' ; ' FROM @EMAIL) AS  [AfterTrimEmail];
GO
--OUTPUT

  • Trailing

In this example, I will show you how to use TRAILING option in TRIM() function. This option removes space(s) / character(s) from the RIGHT side of the column(s) / expression(s) which will be defined in single quotes after TRAILING option, as shown below.


DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = 'KenSanchez@gmail.com;   ';

SELECT @EMAIL  AS [OriginalEmail]
     , TRIM(TRAILING' ; ' FROM @EMAIL) AS  [AfterTrimEmail];

GO
--OUTPUT

  • Both

In this example, I will show you how to use BOTH option in TRIM() function. This option removes space(s) / character(s) from BOTH (left & right) sides of the column(s) / expression(s) which will be defined in single quote after BOTH option, as shown below.


DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = '   ;KenSanchez@gmail.com;   ';

SELECT @EMAIL  AS [OriginalEmail]
     , TRIM(BOTH' ; ' FROM @EMAIL) AS  [AfterTrimEmail];

GO
--OUTPUT



DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = '   ;KenSanchez@gmail.com;   ';

SELECT @EMAIL  AS [OriginalEmail]
     , TRIM(' ; ' FROM @EMAIL) AS  [AfterTrimEmail];

GO
--OUTPUT

  • Note:

If you don’t write any option name (LEADING, TRAILING, BOTH) in the TRIM() function then by default BOTH option will be applied as shown below.

Conclusion:

I found the new enhancement very useful and handy, earlier we used multiple functions to find and remove any character(s) from the start or end of the column(s) \ expression(s) or both sides of the column(s) \ expression(s), now we can achieve it via TRIM() function ONLY. Do let me know if you use this enhancement and how helpful you find it.

Read Full Post »

SQL SERVER – TRIM – How to remove leading and trailing characters/Spaces from string

Posted in Solutions, SQL SERVER, tagged , , , , , , on May 20, 2013| 5 Comments »

LTRIM and RTRIM are very useful functions when it comes to trim the left side or right side of the string spaces respectively but if you need to trim spaces from both sides , you need to create a user defined function. Here is the script of user defined function. I have been using these built-in and user defined functions for a long time. But a  few days ago, when I was migrating the data from legacy system to our new system, I came across with an issue i.e., I had to trim not only the spaces but also the characters as well.

Given below is the solution :

Left Trim :
Given below is the solution that can help you to trim(remove) the spaces and characters from the LEFT side of the string.

CREATE FUNCTION dbo.L_TRIM(@String VARCHAR(MAX), @Char varchar(5))
RETURNS VARCHAR(MAX)
BEGIN
RETURN SUBSTRING(@String,PATINDEX('%[^' + @Char + ' ]%'
,@String),DATALENGTH(@String))
END
GO
--In this example, the user defined function will remove spaces
--and * from the left side of the string
SELECT '  **  THIS IS A TEST STRING  ***  '
AS [String before Left trim]
, dbo.L_TRIM('  **  THIS IS A TEST STRING  ***  ','*')
AS [String After Left trim]
GO
--In this example, the user defined function will remove spaces
--and 0 from the left side of the string
SELECT ' 0001234' AS [String before Left trim]
, dbo.L_TRIM(' 0001234','0')  [String After Left trim]
GO
--OUTPUT

lefttrim1.1

Right Trim :
Given below is the solution that can help you to trim(remove) the spaces and characters from the RIGHT side of the string.

CREATE FUNCTION dbo.R_TRIM(@String VARCHAR(MAX), @Char varchar(5))
RETURNS VARCHAR(MAX)
BEGIN
RETURN REVERSE(SUBSTRING(REVERSE(@String)
,PATINDEX('%[^' + @Char + ' ]%'
,REVERSE(@String)),DATALENGTH(@String)))
END
GO
SELECT '  **  THIS IS A TEST STRING  ***'
AS [String before Right trim]
, dbo.R_TRIM('  **  THIS IS A TEST STRING  ***  ','*')
AS [String after Right trim]
GO
SELECT '12340000 ' AS [String before Right trim]
, dbo.R_TRIM('12340000 ','0') AS [String after Right trim]
GO
--OUTPUT

righttrim1.2

Trim :
Given below is the solution that can help you to trim(remove) the spaces and characters from the BOTH sides of the string.

CREATE FUNCTION dbo.TRIM(@String VARCHAR(MAX), @Char varchar(5))
RETURNS VARCHAR(MAX)
BEGIN
RETURN SUBSTRING(@String,PATINDEX('%[^' + @Char + ' ]%',@String)
,(DATALENGTH(@String)+2 - (PATINDEX('%[^' + @Char + ' ]%'
,REVERSE(@String)) + PATINDEX('%[^' + @Char + ' ]%',@String)
)))
END
GO
SELECT '  **  THIS IS A TEST STRING  ***  '
AS [String before trim]
, dbo.TRIM('  **  THIS IS A TEST STRING  ***  ','*')
AS [String after trim]
GO
SELECT ' 000012340000 ' AS [String before trim]
, dbo.TRIM(' 000012340000 ','0') AS [String after trim]
GO
--OUTPUT

trim1.3

Read Full Post »