SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 FUNC: IsNumber | ParseNumber
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 03/13/2002 :  12:10:08  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000