Finding numbers from a text in SQL Server.

Introduction

We had an odd requirement to find out the ids from javascript formula, which is used in computation of some values the web page and compare that with the backend mappings. There were some discrepancy due to oversight. Dont ask me why we designed our application it this way..that is a long story and we are moving away from the current design.

We had written an SQL script to make the corrections, for which I have modifed a function found in the link http://blog.sqlauthority.com/2007/07/07/sql-server-convert-text-to-numbers-integer-cast-and-convert/ . This function will return a table with all the numbers found on the input text.

Example 1:

SELECT Number FROM Dbo.[GetNumbersFromText]('The dimension is 230x40x30')

Number
-------
230
30
40

Example 2:

SELECT Number FROM Dbo.[GetNumbersFromText]('Give me 120 this week and 50 next week')

Number
-------
120
50

Function

CREATE FUNCTION [dbo].[GetNumbersFromText](@String VARCHAR(2000))
RETURNS @Number TABLE (Number INT)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''
WHILE @Count <= LEN(@String)
BEGIN
--Find a numeric charactor
IF SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
END
--If the next charactor is not a numeric one, the current number ends, so add a separator
IF (SUBSTRING(@String,@Count+1,1) < '0'OR SUBSTRING(@String,@Count+1,1) > '9') AND SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + ','
END
SET @Count = @Count + 1
END
---Split string to give a table with the numbers in the text
INSERT INTO @Number
SELECT DISTINCT items FROM dbo.Split(@IntNumbers, ',')
return
END

Note that this function uses a scalar valued function dbo.Split which splits a varchar at a given separator. You may already have this or a similar function, else it is a google search away.