Click here to Skip to main content
Click here to Skip to main content

Finding numbers from a text in SQL Server.

, 8 Mar 2012
Rate this:
Please Sign up or sign in to 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Saral S Stalin
Technical Lead
India India
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140709.1 | Last Updated 8 Mar 2012
Article Copyright 2012 by Saral S Stalin
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid