SQL FUNCTION ExtractNumbers

SQL FUNCTION ExtractNumbers

Create Function ExtractNumbers

(

@str_passed varchar(max)

)

Returns varchar(50)

AS

BEGIN

    declare @val varchar(50), @i int

    select @val = '', @i = 1

    while (@i <= len(@ str_passed))

        select @val= @val+ (CASE when substring(@ str_passed,@i,1) like '[0-9]' 

                                  then substring(@ str_passed,@i,1) else '' END),

              SET @i = @i + 1

    Return @val

END

Usage:-

SELECT SrNo,  dbo. ExtractNumbers(AlphaNumCol) as NumColData from TableName

Drop commas from '45,22,135'

or Drop dash & braces from '(91)123-456-789'

or Drop alphabets from 'C99B33A1234'