Get numeric value from string using TSQL

Introduction

Many times while working on applications, we often land into a situation where we want to get the only numeric values from alpha numeric string. There are some approaches given, like you may achieve it with making a loop and without loop.

Approaches

I have prescribed as a simple query, but you will achieve same by making a function.

1. Get the numbers through loop.

DECLARE @AlphaNumericString VARCHAR(MAX) = 'ABC1DEF2GHI3'

DECLARE @NumericValueIndex INT

SET @NumericValueIndex = PATINDEX('%[^0-9]%', @AlphaNumericString)

WHILE @NumericValueIndex > 0

  BEGIN

    SET @AlphaNumericString = STUFF(@AlphaNumericString, @NumericValueIndex, 1, '' )

    SET @NumericValueIndex = PATINDEX('%[^0-9]%', @AlphaNumericString )

  END

SELECT ISNULL(@AlphaNumericString,0) 'Numeric Values'

OUTPUT

2. Get the numbers without loop.

DECLARE @AlphaNumericString VARCHAR(MAX) = 'ABC1DEF2GHI3'

DECLARE @NumericValues VARCHAR(MAX) = ''

SELECT

  @NumericValues =

    @NumericValues +

      CASE WHEN NumericValue like '[0-9]' THEN NumericValue ELSE '' END FROM

      (

        SELECT

          SUBSTRING(@AlphaNumericString,NumericValue,1) NumericValue

        FROM

        (

          SELECT

            number NumericValue

          FROM

            master..spt_values

          WHERE 

            TYPE = 'p' 

            AND number BETWEEN 1 AND LEN(@AlphaNumericString)

        ) T

      ) T

SELECT ISNULL(@NumericValues,0) 'Numeric Values'

OUTPUT

Limitation

There is a limitation in 2nd approach, the table “master..spt_values” contains only 2048 rows value. So if you want to get the numbers from the very big text contains above 2048 characters, then this approach is not suitable.

To over come this limitation, you can use “Tally Table” instead of “master..spt_values”. Replace “master..spt_values” with “Tally Table”.

Want to get more information about Tally Table? Click here.

Conclusion

If you have the need of numeric values only from less characters of 2048, then no need to process for “Tally Table”. Just use the “master..spt_values”.