SQL Server: UDF IsValidNumber

Learn how to check if a value is a valid number.

--This function accepts a string and checks it to see if it contains
--characters that are not 0 - 9 or a decimal point .
--RETURNS: 0 -- Valid Number
-- 1 -- Invalid Number
--
CREATE FUNCTION udfIsValidNumber
(
@thestring varchar(50),
@numdecimals int = 0
)

RETURNS int
AS
BEGIN
DECLARE @not int,
@ascii int,
@pos int,
@dec int
SET @pos = 1
SET @not = 0
SET @dec = 0
--first check to see if it is a valid number
IF @thestring IS NULL
SET @not =1
IF len(@thestring) = 0
SET @not = 1
WHILE @pos<= len(@thestring)
BEGIN
SELECT @ascii = ascii(substring(@thestring, @pos, 1))
IF (@ascii > 57) SET @not = 1
IF (@ascii < 46) SET @not = 1
IF (@ascii = 47) SET @not = 1
IF (@ascii = 46) SET @dec = @dec + 1
SET @pos = @pos + 1
END
IF @dec > 1 SET @not = 1
IF @not > 0 RETURN @not -- invalid number
--valid number now check number of decimals

SELECT @dec = charindex('.',@thestring)
SET @pos = len(@thestring) - @dec -- find the number of characters right of decimal
IF @pos > @numdecimals SET @not = 1

RETURN @not
END





Submission Date:  9/23/2005 3:23:59 PM
Submitted By:  Peter Bromberg
My Home Page:  http://www.eggheadcafe.com