Finding numbers from a text in SQL Server.





5.00/5 (1 vote)
The below function will find numbers from a given text in table format.
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.