SQL Trim UDF

.NET strings have a method that allow you to trim characters from the beginning and end of a string. There is also a SQL trim function, but you can only trim the space character, unfortunately you cannot specify which character you want to trim. I sought to rectify this with a UDF:

CREATE FUNCTION TrimSingleChar
(
-- Add the parameters for the function here
@needle nchar(1),
@haystack as nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @pos as int -- variable to hold the position of @needle

-- Check whether @needle appears at the start of the string
SELECT @pos = CHARINDEX(@needle,@haystack,0)
IF @pos = 1
BEGIN
SELECT @haystack = SUBSTRING(@haystack,2,LEN(@haystack) -1)
END

-- Reverse the string
SELECT @haystack = REVERSE(@haystack)

-- Check again whether @needle appears at the start of the string
SELECT @pos = CHARINDEX(@needle,@haystack,0)
IF @pos = 1
BEGIN
SELECT @haystack = SUBSTRING(@haystack,2,LEN(@haystack) -1)
END

-- Reverse the string to back how it was, and return it
RETURN REVERSE(@haystack)

END
GO

Unfortuantely at the moment this function will only trim a single instance of @needle from the beginning and end, but at the moment that’s all I need.

As .NET has native functions available to perform the above task, a better option would be to use a SQL CLR function. That’s beyond the scope of this article, however. Maybe next time! For more information about SQL CLR functions, you can check out more at this link: http://msdn.microsoft.com/en-us/library/ms131077.aspx

For more information about SQL UDF functions, you can read more here: http://technet.microsoft.com/en-us/library/aa214363(v=sql.80).aspx