Friday, December 4, 2009

Extract numbers from a string


--SELECT [dbo].[fnc_GetNumberValueFromString] ('Price: $121/2.5')
 --============================================================
--Following function extracts numbers from a string
--=============================================================
CREATE FUNCTION [dbo].[fnc_GetNumberValueFromString]
                (
                                @string NVARCHAR(200)
                )
RETURNS REAL
AS 
BEGIN

DECLARE @ResultNumber REAL
DECLARE @RequiredString  NVARCHAR(100)
DECLARE @position int
DECLARE @length int
DECLARE @char nchar(1)
               
SET @position = 1
SET @length = LEN(@String)
SET @ResultNumber=0.00
SET @RequiredString = 0

DECLARE @pointCount INT
SET @pointCount = 0

-- Add the T-SQL statements to compute the return value here
WHILE @position <= @length
BEGIN
 SET @char = SUBSTRING(@String, @position, 1)

IF (@char <= '9' and @char >= '0') OR @char = '.'
  BEGIN   
     IF @char= '.'
        BEGIN
                                    IF @pointCount <=1
                                     BEGIN    
                                                SET @RequiredString = @RequiredString + '.'
                                                SET @pointCount = @pointCount +1
                                     END       
        END
    ELSE
        SET @RequiredString = @RequiredString + @char
   END
SET @position = @position + 1
END        
            SET @ResultNumber = CAST(@RequiredString AS REAL)
            RETURN @ResultNumber

END