Monthly Archives: December 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
Advertisements