>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

Posted on December 4, 2009, in TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: