Friday, April 6, 2012

Sqlserver TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds

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 

 Reference : Pinal Dave (http://blog.SQLAuthority.com)

No comments: