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


  1. I’ve used this kind of tricks before. I hope its forgiving.

Reply to frank ¬
Cancel reply

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>