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 :
No comments:
Post a Comment