FUNC: IsNumber | ParseNumber - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

FUNC: IsNumber | ParseNumber

Author

Topic

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-03-13 : 12:10:08

I remember seeing all the complaints about ISNUMERIC. Well here is a function that should work much better. You can imagine you could write a function called ParseFloat, ParseInt, ParseMoney, etc... to convert the strings that validate with IsNumber. You may also want to expand this to make sure that $ and - don't exist in the string other than at the beginning. Also might want to make sure the . is only included once. But for the basics this should spawn some very helpful ideas... well enjoy!


CREATE FUNCTION [dbo].[IsNumber] (@Value VARCHAR(8000)) 
    RETURNS BIT AS
    BEGIN
        DECLARE @str VARCHAR(8000)
        DECLARE @bit BIT
 
        SET @str = REPLACE(REPLACE(REPLACE(REPLACE(@Value, '$', ''), '-', ''), '.', ''), ',', '')
        SET @bit = 0
 
        IF CHARINDEX('$', @Value) > 0
            BEGIN
                IF CHARINDEX('$', @Value) > 2 SET @Value = ''
                IF CHARINDEX('$', @Value) > 1 AND CHARINDEX('-', @Value) <> 1 SET @Value = ''
                IF CHARINDEX('$', @Value, CHARINDEX('$', @Value) + 1) > 0 SET @Value = ''
            END
 
        IF CHARINDEX('-', @Value) > 0
            BEGIN
                IF CHARINDEX('-', @Value) > 2 SET @Value = ''
                IF CHARINDEX('-', @Value) > 1 AND CHARINDEX('$', @Value) <> 1 SET @Value = ''
                IF CHARINDEX('-', @Value, CHARINDEX('-', @Value) + 1) > 0 SET @Value = ''
            END
 
        IF CHARINDEX('.', @Value) > 0
            BEGIN
                IF CHARINDEX('.', @Value, CHARINDEX('.', @Value) + 1) > 0 SET @Value = ''
            END
 
        SELECT @bit = 1
         WHERE @str NOT LIKE '%[^0-9]%'
 
        IF @bit IS NULL OR LEN(@str) < 1 SET @bit = 0
        RETURN @bit
    END

<update>Updated to check for proper characters [$-.] and to handle comma separators.</update>

<update>Here is a ParseNumber function that works in the same manner. This one handles decimal numbers with a precision of 38 and scale of 8 ... feel free to change it to what ever your needs are...</update>


CREATE FUNCTION [dbo].[ParseNumber] (@Value VARCHAR(8000)) 
    RETURNS DECIMAL(38, 8) AS
    BEGIN
        DECLARE @bit    BIT
        DECLARE @number DECIMAL(38, 8)
 
        SELECT @bit    = [dbo].[IsNumber](@Value),
               @number = NULL
 
        IF @bit = 1 SET @number = CONVERT(DECIMAL(38, 8), REPLACE(REPLACE(@Value, '$', ''), ',', ''))
 
        RETURN @number
    END

Edited by - onamuji on 03/13/2002 14:37:42

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources