JoelPadot.com

SQL Server 2005 Strip Alphabetical Characters from a String

For anyone who has ever had to do funky things in SQL, you’ll probably appreciate this post. However, you may have already written something similar before. Oh well, I’m bored at the moment and feel like sharing this little user-defined-function (UDF) that makes use of the PATINDEX() function to strip alpha characters from a varchar string.

Why would you need to strip the alphabetical characters from a string? In my scenario, departments and their division are stored in a single column and identified by a number to indicate department and a letter to indicate division (example: 15a = Information Services – Applications Division).

Say you need to find out what department a division is a member of. All you need to do is strip the alpha characters from the string and you’re left with the department the division belongs to. Let me also throw it out there that SUBSTRING() won’t work because we may have between 1 and 3 digit numbers for department numbers.

The following user-defined-function (UDF) has served me well for this scenario. So, I figure it is only fair to pass it on.

create function fnStripAlpha
(
     @string varchar(MAX)
)
returns varchar(MAX)
as
begin
     while patindex('%[^0-9]%’, @string) > 0
          set @string = replace(@string,substring(@string,patindex(’%[^0-9]%’, @string),1),”)
     return @string
end