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;
stringValue numberValue ------------ ------------ INW80932A 80932 INW23A32 2332 INW53A89KJ31 538931 IT78A25Q3 78253 IT897SA24 89724 IT564SF891A 564891