Monthly Archives: March 2009

Function to remove letters from a string in SQL Server

 I found a function at

that removes letters from a varchar in SQL Server.  I found it to be a bit slow for my needs.  So, I made the following function that performs better for me.

alter function NoLetters(@string2number varchar(200)) returns varchar(200)
 declare @c int
 declare @num varchar(200)
 set @num = ”
 declare @txtCurrent char(1)
 set @c=1
 while @c<=len(@string2number)
  set @txtCurrent = substring(@string2number,@c,1)
  if ascii(@txtCurrent) between 48 and 57
   set @num = @num + @txtCurrent

  set @c=@c+1
return @num

To use the function it can be added to a print statement like
PRINT dbo.NoLetters(‘ABC1234′)
This will return 1234