After reading my article SQL SERVER - 2008 - TRIM() Function - User Defined Function, I have received email and comments where user are asking if it is possible to remove trailing spaces, leading spaces, white space, tabs, carriage returns, line feeds etc.
I found following script posted by Russ and Erik. It is modified a bit from original script.
CREATE FUNCTION dbo.LTrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @trimchars VARCHAR(10)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE ‘[' + @trimchars + ']%’ SET @str = SUBSTRING(@str, PATINDEX(‘%[^' + @trimchars + ']%’, @str), 8000)
RETURN @str
END
GO
CREATE FUNCTION dbo.RTrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @trimchars VARCHAR(10)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE ‘%[' + @trimchars + ']‘
SET @str = REVERSE(dbo.LTrimX(REVERSE(@str)))
RETURN @str
END
GO
CREATE FUNCTION dbo.TrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN dbo.LTrimX(dbo.RTrimX(@str))
END
GO
/* Run the created function */
SELECT dbo.TRIMX(‘ word leading trailing spaces ’) AS ‘TrimmedWord’
GO
Running above function will give you result where there is no white spaces or any other while space like chars.
Let me know what you think about this and if this can be improved further.
Reference : Pinal Dave (http://www.SQLAuthority.com)
pinaldave
Hi Pinal,
May I suggest some improvements?
I tend to call my whitespace trimmers “SuperTrim” functions, so please excuse the difference in naming.
(This will probably lose its indenting when I paste it.)
———
CREATE FUNCTION dbo.SuperTrimLeft(@str varchar(MAX)) RETURNS varchar(MAX)
AS
BEGIN
IF (ASCII(LEFT(@str, 1)) < 33) BEGIN
SET @str = STUFF(@str, 1, PATINDEX(’%[^'+CHAR(0)+'-'+CHAR(32)+']%’, @str) - 1, ”);
END;
RETURN @str;
END;
———
I think it’s a big performance increase, because it does a numeric test of the first character, rather than a relatively expensive pattern test. If the first character is not a space or some other control character, no pattern is tested at all.
Then, instead of testing for specific control characters, why not test for the whole range of zero (0) through 32 (space)?
The function above does the test for first non-whitespace char and the trimming all in one statement.
I couldn’t think of a faster or more efficient way to do it. I did some testing, and it seemed to work well.
I haven’t looked at different ways to do SuperTrimRight yet, but the ways you outlined should work with this function OK.
Thanks for the great SQL blog!
-Todd (”Speednet”)
Oh dear, surely, unless I’m missing something these functions will just return the first 8000 characters if they do a Trim, and so could cause havoc if someone uses it in SQL Server 2005/8 without realising its limitations, or you have it in place in a production system and the data changes. You’d do better if it took and returned Varchar(8000). Then it might flag up the weakness in the algorithm.
The function definitely just returns the first 8000 characters. This version definitely works a lot better
alter FUNCTION dbo.LTrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @trimchars VARCHAR(10)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE ‘[' + @trimchars + ']%’
SET @str =STUFF ( @str , 1 , PATINDEX(’%[^' + @trimchars + ']%’, @str)-1 ,” )
RETURN @str
END
GO
Pinal, thanks for all your posts. May I suggest using the spelling “Enhanced” instead of “Enhenced” in your post?
Thanks again!
@phil Factor: If you try my version of SuperTrim, you will see that it does NOT trim to 8000 characters.