True WHOLE NUMBER function.

Question

True WHOLE NUMBER function.

pcq0125

SSCrazy

Points: 2890
More actions
July 26, 2008 at 8:30 am

#381949

I need to create a true WHOLE NUMBER function that does not contains -+?/ and all operators or funny characters.After I created the function below it seems like it will still accept '+' or anything returns 0 or >. Please advise. Thanks.
select dbo.fn_ISTRUEWholeNumber(+6)
and the result returns 1
Below is my function
-------------------------------------------
-- =============================================
-- Drop Function
-- =============================================
IF EXISTS ( select * from information_schema.routines
where SPECIFIC_SCHEMA = 'dbo' and SPECIFIC_NAME = N'fn_ISINTEGER' and ROUTINE_TYPE = 'FUNCTION ' )
DROP FUNCTION dbo.fn_ISTRUEWholeNumber
go
-- =============================================
-- Create Function
-- =============================================
CREATE FUNCTION dbo.fn_ISTRUEWholeNumber
(
@strToBeEval varchar(1000) -- enlarge this if needed
)
RETURNS bit
AS
BEGIN
DECLARE @bitReturn bit
IF @strToBeEval LIKE '%[^0-9]%'
SET @bitReturn = 0
ELSE
SET @bitReturn = 1
RETURN @bitReturn
END
GO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

You already have a true whole number function and it works well. The only problem is with how you tested it... you've passed a numeric value to the function and +6 is not a naturally occuring numeric. The "+" is nothing more than a formatting symbol in this case.

Any conversion from +6 without single quotes to a varchar whether in your function or not, will result in just a '6'. Further, no natural numeric datatype will actually store the "+" sign so, if it's a number, you don't have to worry about testing for a plus sign.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pcq0125 SSCrazy Points: 2890 More actions · Answer 2

pcq0125

SSCrazy

Points: 2890

July 26, 2008 at 9:59 am

#848779

THanks.