Tip/Trick

# Finding numbers from a text in SQL Server.

, 8 Mar 2012 CPOL
 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

Technical Lead
India
No Biography provided

## Comments and Discussions

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