SQL Trim with Alternative Characters
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