Home » SQL & PL/SQL » SQL & PL/SQL » isDigit () method in PL/SQL
isDigit () method in PL/SQL [message #124939] Wed, 22 June 2005 07:22 Go to next message
sreek_s
Messages: 45
Registered: May 2005
Location: Andaman Nikobar
Member
Hi,
I need to write a PLSQL Function which checks whether the string passed is a digit or not.

Suppose if i pass '10' it should return true.
If i pass 'XY10' it should return false.

Pls help me out.

Regds,
Srikanth
Re: isDigit () method in PL/SQL [message #124946 is a reply to message #124939] Wed, 22 June 2005 08:09 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hai Sree,

I thin U can create function like this if u want to test in SQL * plus environment...

SQL> create or replace function isdigit(v varchar2) return varchar2 is
  2  v1 varchar2(10);
  3  begin
  4    select translate(trim(v),'1234567890',' ') into v1 from dual;
  5    IF  v1 =' ' THEN
  6        RETURN v||' is NUMBER';
  7     ELSE
  8         RETURN v||' is not NUMBER';
  9     END IF;
 10   END;
 11  /

Function created.

SQL> select isdigit('10') NumOrNot from dual;

NUMORNOT
--------------------------------------------------------------------------------
10 is NUMBER

SQL>  select isdigit('XY10')  NumOrNot from dual;

NUMORNOT
--------------------------------------------------------------------------------
XY10 is not NUMBER

SQL> 


If U want U can return Boolean but it is not supported by SQL.
It can be called from anonymous block or procedures/functions

with rgrds
Rajuvan
Re: isDigit () method in PL/SQL [message #124948 is a reply to message #124939] Wed, 22 June 2005 08:17 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
You have to be very specific about what you mean by "digit". Are decimals acceptable? What about the positive or negative sign? Scientific notation?
SQL> CREATE OR REPLACE FUNCTION is_digit (
  2      p_candidate     IN  VARCHAR2
  3  )
  4  RETURN BOOLEAN
  5  IS
  6      l_dummy_number      NUMBER;
  7      l_is_digit          BOOLEAN;
  8  BEGIN
  9      BEGIN
 10          l_dummy_number := TO_NUMBER(p_candidate);
 11          l_is_digit := TRUE;
 12      EXCEPTION
 13          WHEN OTHERS THEN
 14              l_is_digit := FALSE;
 15      END;
 16      RETURN (l_is_digit);
 17  END is_digit;
 18  /
 
Function created.
 
SQL> CREATE OR REPLACE FUNCTION bool_to_string (
  2      p_boolean   IN BOOLEAN
  3  )
  4  RETURN VARCHAR2
  5  IS
  6      l_bool_as_string    VARCHAR2(7);
  7  BEGIN
  8      IF (p_boolean IS NULL) THEN
  9          l_bool_as_string := 'NULL';
 10      ELSIF (p_boolean) THEN
 11          l_bool_as_string := 'TRUE';
 12      ELSIF (NOT p_boolean) THEN
 13          l_bool_as_string := 'FALSE';
 14      ELSE
 15          l_bool_as_string := 'UNKNOWN';
 16      END IF;
 17      RETURN (l_bool_as_string);
 18  END bool_to_string;
 19  /
 
Function created.
 
SQL> SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
SQL> BEGIN
  2      DBMS_OUTPUT.PUT_LINE('is_digit(4)      = ' || bool_to_string(is_digit('4')));
  3      DBMS_OUTPUT.PUT_LINE('is_digit(3.5)    = ' || bool_to_string(is_digit('3.5')));
  4      DBMS_OUTPUT.PUT_LINE('is_digit(2E5)    = ' || bool_to_string(is_digit('2E5')));
  5      DBMS_OUTPUT.PUT_LINE('is_digit(E5)     = ' || bool_to_string(is_digit('E5')));
  6      DBMS_OUTPUT.PUT_LINE('is_digit(-1)     = ' || bool_to_string(is_digit('-1')));
  7      DBMS_OUTPUT.PUT_LINE('is_digit(0.0)    = ' || bool_to_string(is_digit('0.0')));
  8      DBMS_OUTPUT.PUT_LINE('is_digit(7.7e-6) = ' || bool_to_string(is_digit('7.7E-6')));
  9      DBMS_OUTPUT.PUT_LINE('is_digit(+59)    = ' || bool_to_string(is_digit('+59')));
 10      DBMS_OUTPUT.PUT_LINE('is_digit(1+1)    = ' || bool_to_string(is_digit('1+1')));
 11      DBMS_OUTPUT.PUT_LINE('is_digit(2/3)    = ' || bool_to_string(is_digit('2/3')));
 12  END;
 13  /
is_digit(4)      = TRUE
is_digit(3.5)    = TRUE
is_digit(2E5)    = TRUE
is_digit(E5)     = FALSE
is_digit(-1)     = TRUE
is_digit(0.0)    = TRUE
is_digit(7.7e-6) = TRUE
is_digit(+59)    = TRUE
is_digit(1+1)    = FALSE
is_digit(2/3)    = FALSE
 
PL/SQL procedure successfully completed.
 
SQL>
Re: isDigit () method in PL/SQL [message #124951 is a reply to message #124939] Wed, 22 June 2005 08:37 Go to previous messageGo to next message
sreek_s
Messages: 45
Registered: May 2005
Location: Andaman Nikobar
Member
Guyz,
Thanks a lot...

Regds,
Srikanth
Re: isDigit () method in PL/SQL [message #124953 is a reply to message #124939] Wed, 22 June 2005 08:39 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Sorryyyyyy

It should be like this....

SQL> ed
Wrote file afiedt.buf

  1  create or replace function isdigit(v varchar2) return varchar2 is
  2  n number;
  3  BEGIN
  4  select length(translate(UPPER(v),'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ','0000000000')) into n from dual;
  5   IF  length(v)=n  THEN
  6       RETURN v||' is NUMBER';
  7   ELSE
  8       RETURN v||' is not NUMBER';
  9   END IF;
 10* END;
SQL> /

Function created.

SQL> 


Rajuvan
Re: isDigit () method in PL/SQL [message #164604 is a reply to message #124939] Fri, 24 March 2006 04:46 Go to previous messageGo to next message
riteshambastha
Messages: 2
Registered: March 2006
Location: MUMBAI/INDIA
Junior Member

Thanks alot. you solved my problem
Re: isDigit () method in PL/SQL [message #164800 is a reply to message #124953] Sun, 26 March 2006 12:59 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Art's version handled scientific notation and NLS formatting issues such as the thousands separator character, and also avoided a database query.

I think "digit" is a misleading term here, if what is meant is a number. Perhaps IS_NUMBER would be clearer.
Re: isDigit () method in PL/SQL [message #433307 is a reply to message #124939] Tue, 01 December 2009 06:56 Go to previous messageGo to next message
dbazim
Messages: 1
Registered: December 2009
Location: Moscow
Junior Member
A little bit simpler code.

function isdigit(v varchar2) return boolean is
begin
return (upper(v) = lower(v));
END;

Re: isDigit () method in PL/SQL [message #433309 is a reply to message #433307] Tue, 01 December 2009 07:02 Go to previous message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
1. Ressurecting a three year old thread. Really?

2. try your function with the following strings :
'()*&^%$' or '2E5'
Now read Art's post again.

3. Regular expressions would be the way to go nowadays.
Previous Topic: commit. Please help
Next Topic: Can we call a stored function in the constraint of a table?
Goto Forum:
  


Current Time: Mon Sep 26 19:39:34 CDT 2016

Total time taken to generate the page: 0.15477 seconds