How can I get only numbers from a string as a computed column in SQL Server without leading zeros?

Background

I currently have a function to get only numbers (no leading zeros) from a string (let's call two variables @alphaNumeric

and @strippedNumber

).

It turns out that it is more efficient to have a @strippedNumber

pre-calculated in the table I am querying from than to calculate it on the fly using my function (let's call it dbo.fnFormatNumeric

).

Setting up my calculated column I tried it like this:

ALTER TABLE dbo.Data
ADD StrippedNumber AS CONVERT(BIGINT, dbo.fnFormatNumeric(AlphaNumeric))

      

        
        
        
      

    

This is really effective when converting large amounts of data (on the order of 25,000,000 rows).

Problem occurs when trying to update my database (we are dropping and recreating all functionality). So I tried to exclude this feature from dumping or recreating.

Mistake. Now I cannot install the new database as it depends on a function that was not created.

Ideal scenario

I would like to be able to select all numbers from a row as a calculated column in my table without using a function.

Study

The Sql Authority Blog offers a function to use to get numeric values, which this answer is on SO . This is not good as the function is causing me problems.

Another answer on SO suggests using LEFT

and PATINDEX

, but that will only give me the first set of numbers, not all of them in the string.

This question on SO does not fit as it ISNUMERIC

only uses to get strings where everything is numeric.

I found a blog post where they use PATINDEX

to get numbers from a string, but that assumes all the numbers are together.

Inputs and expected outputs:

@alphaNumeric  =>  @strippedNumber
-----------------------------------
FXADJ011016CR  =>   11016
15-June-2016   =>   152016
708014         =>   708014
FXRWECTB       =>   (empty string)

      

        
        
        
      

    

Existing function:

Input @alphaNumeric varchar(255)    

    DECLARE     @strtoCheckLength       Int,
                @strCount               Int,
                @code                   Int,
                @StrippedNumber varchar(255)
    -- Get and set length and loop variables
    Set @strtoCheckLength = Len(@alphaNumeric)
    Set @strCount = (0)
    Set @StrippedNumber = ''
    -- Make sure we only include Numerics
    While @strCount <= @strtoCheckLength
        Begin
            set @code = Ascii(SubString(@alphaNumeric, @strCount, 1))
            If (@code between 48 and 57) 
                Begin
                    set @StrippedNumber = @StrippedNumber + Substring(@alphaNumeric, @strCount, 1)
                End
            Set @strCount = (@strCount + 1)
        End
    -- Remove Leading Zeros
    While (Len(@StrippedNumber) > 0) And (Left(@StrippedNumber, 1) = '0')
        Begin
            Set @StrippedNumber = Right(@StrippedNumber, Len(@StrippedNumber) - 1)
        End
    Return @StrippedNumber