How can I get only numbers from a string as a computed column in SQL Server with no leading zeroes?

Question

Background

Currently I have a function to get only numbers (with no leading zeroes) from a string (let's call the two variables @alphaNumeric and @strippedNumber).

It turns out that it is more efficient to have @strippedNumber pre-calculated on the table that I'm querying from than to calculate it on the fly using my function (let's call this dbo.fnFormatNumeric).

Setting up my computed column I tried it as follows:

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

This is really efficient at doing the conversion of large amounts of data (in the order of 25,000,000+ rows).

The problem comes when trying to upgrade my database (we drop and recreate all functions). So I tried to exclude that function from being dropped or recreated.

Mistake. Now I can't install a new database because it's reliant on a function that hasn't been created.

Ideal scenario

I would like to be able to select all numbers from a string as a computed column on my table without using a function.

Research

Sql Authority Blog suggests a function for use to get numeric values, which this answer on SO references. This is no good as a function causes me problems.

Another answer on SO suggests using LEFT and PATINDEX but this will only get me the first set of numbers and not all of them in the string.

This question on SO is no good as it uses ISNUMERIC to only get strings where all of it is numeric.

I found a blog post where they use PATINDEX to get the numbers from a string, but this is assuming that 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

Answer 1

Hopefully this snippet will help you define your computed column.

Essentially, the numbers table is used to break up the string by indexing, then the query casts the output of the concatenation to bigint to remove the leading zeroes before recasting to a varchar(255)

In the production database I would advocate having the numbers table pre-defined and populated. There is a lot of discussion around how to use them on www.SqlServerCentral.com

--set up a numbers table
select top 50 n=identity(int,1,1) into #temp_numbers from syscolumns

/*
FXADJ011016CR => 11016

15June2016 => 152016

708014 => 708014

FXRWECTB => Empty String
*/

declare @input varchar(50) = 'FXADJ011016CR'
declare @output varchar(50)

select isnull(cast(cast((
    select numericValue as [text()] from (
        select substring(@input, t.n, 1) as numericValue 
            from #temp_numbers t 
            where isnumeric(substring(@input, t.n, 1))=1
        ) tblResults for xml path('')
    ) as bigint) as varchar(255)),'')

drop table #temp_numbers