Simple problem...

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Thu, 27 Feb 2003 17:05:24 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA7035BFB48@lnewton.leeds.lfs.co.uk>

Afternoon T,

Unfortunately, I have had to roll my own function(s) to check - similar to the stuff below.

I normally use TO_NUMBER in its own begin/exception/end block and trap the exceptions. Problem with Integer numbers is that they are also floats (so to speak) so is_a_float('100') should return true as would is_an_integer('100').

In my version of IsAFloat, I allow the caller to specify whether integers are valid or not - the default is currently true, but can easily be changed to false.

IsANumber() is not anything I've had to write - just call IsAFloat('whatever', true) instead.

CREATE OR REPLACE FUNCTION IsAFloat(What IN VARCHAR2, IntIsFloat IN BOOLEAN DEFAULT TRUE)
RETURN BOOLEAN
AS

• ==============================================
• This function return 1 if the value passed in
• is a proper float representation. It will flag
• integers as floats unless told otherwise.
• ============================================== MyNumber NUMBER; MyOffset NUMBER := 1; BEGIN MyNumber := TO_NUMBER(What); IF (MyNumber = ROUND(MyNumber,0)) THEN
  • This is an integer number IF (IntIsFloat) THEN
    • Integers are allowed RETURN TRUE; ELSE
    • Integers are not allowed RETURN FALSE; END IF; ELSE
  • This is a float number RETURN TRUE; END IF; EXCEPTION WHEN VALUE_ERROR THEN RETURN FALSE; END; /

CREATE OR REPLACE FUNCTION IsAnInteger(What IN VARCHAR2) RETURN BOOLEAN
AS

• ==============================================
• This function returns 1 if the value passed in
• is a proper integer representation.
• ============================================== MyNumber NUMBER; BEGIN MyNumber := TO_NUMBER(What); IF (MyNumber = ROUND(MyNumber,0)) THEN RETURN TRUE; ELSE RETURN FALSE; END IF; EXCEPTION WHEN VALUE_ERROR THEN RETURN FALSE; END; /

Might be useful - maybe ?

Cheers,
Norman.

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

-----Original Message-----
From: Telemachus [mailto:tollg_at_tendwa.rns.net] Posted At: Thursday, February 27, 2003 3:37 PM Posted To: server
Conversation: Simple problem...
Subject: Simple problem...

But I can't find an easy solution.

For 817 without rolling your own PL/SQL func... Is there a fast builtin way to do IS_A_NUMBER('STRING') or is_a_float() or
is_an_integer()

i.e. given a string return true if the string represents a valid number or
false. Or do most people trap an error from TO_NUMBER ?

T ... Received on Thu Feb 27 2003 - 11:05:24 CST