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”.