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
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
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
SQL Server Portal 