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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.02 seconds. Powered By: Snitz Forums 2000