JoelPadot.com

SQL UDF to strip all Non Alpha-numeric Characters

A few days ago, I introduced a SQL user-defined function for stripping alphabetical characters from a varchar. Today I’d like to throw a little something something out there for stripping all non alpha-numeric characters from a varchar. Now, you may be wondering when you would use something like that, but there may come a time. My situation was that I had a developer who wanted what was stored as a comma delimited list to be returned as a string with no commas. Now, some of you may be thinking that the commas were there for the purpose of handling multi-character items, but hey. I was just the database guy for this little project. Regardless, I used the following user-defined function (UDF) to satisfy the request.

CREATE FUNCTION [dbo].[fn_parseAlphaChars]
(
	@string varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
     declare    @charPos int
     set @charPos = patindex(’%[^0-9A-Za-z]%’, @string)
     while @charPos > 0 begin
          set @string = stuff(@string, @charPos, 1, ”)
          set @charPos = patindex(’%[^0-9A-Za-z]%’, @string)
     end
     set @string = @string
     return @string
END

Something I’d like to point out is the stuff() function used in the UDF. its not all that common of a function (at least that I know of). What the stuff method does is take expr4 and insert it into the space between expr2 and expr3 of expr1. As if I couldn’t explain that more confusing let me illustrate.

stuff(string expr1, int expr2, int expr3, string expr4)

Example:

stuff('12345', 2, 3, 'abcde')

the above function will return:

1abcde5

That’s about all I got for this post. I hope you find it useful.