mbielanczuk

Yet Another Numbers Extraction Script in SQL

Since Linq has become very popular for .NET application I have less and less often chance to write something in SQL on Ms Sql Server. Last time I had that chance when i needed to write function for extracting number parts from string.

I decided to approach this subject professionally, so first i’ve asked uncle google for help. He gave me lots of answers, from which I’ve picked one, most interesting in my opinion which was: ‘Extracting numbers with SQL Server’ I think it’s really great article.

There was almost everything i wanted, except that I needed to have all numer parts from string, not only first one.
After few changes (added while loop and replaced LEFT with REPLACE function) final function looks like this:

CREATE FUNCTION [dbo].[EXTRACTNUM] (@str varchar(4000))  
  RETURNS VarChar(4000)  
  AS 
  BEGIN
    WHILE PATINDEX( '%[^0-9]%', @str ) > 0 
    SET @str = REPLACE( @str, SUBSTRING( @str
      ,PATINDEX( '%[^0-9]%', @str )
      , 1 )
     , '' ) 
    RETURN @str
  END

and it can be used like that:

Declare @testData Table(stringValue VarChar(12));

Insert Into @testData Values('INW80932A');
Insert Into @testData Values('INW23A32');
Insert Into @testData Values('INW53A89KJ31');
Insert Into @testData Values('IT78A25Q3');
Insert Into @testData Values('IT897SA24');
Insert Into @testData Values('IT564SF891A');

SELECT stringValue, 
       dbo.EXTRACTNUM(stringValue) as numberValue 
FROM @testData;

Result

stringValue    numberValue
------------   ------------
INW80932A      80932
INW23A32       2332
INW53A89KJ31   538931
IT78A25Q3      78253
IT897SA24      89724
IT564SF891A    564891