Saturday, 23 July 2011

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

Print01

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

Print01

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

No comments:

Post a comment