Search the Site

Sponsors

bottom corner

Custom is_number() and is_date() functions in Oracle

Some programming and scripting languages provide built-in functions that can help you determine whether a particular value is a number or a date. Oracle SQL does not offer similar functionalities out of the box, but these simple functions can achieve similar results.

This page is filed under keyword(s): oracle.

To do so, create this following two functions. The first function tests for numbers, and the second tests for dates. You should be sure to change the schema name to the correct value for your environment.

create or replace function schema1.is_number(in_var in varchar2)
return varchar2
is
  v_number number;
begin
  select to_number(in_var) into v_number from dual;
  return 'Y'; -- No exception, so is a number
exception
  when others then
  return 'N'; -- is not a number
end;

create or replace function schema1.is_date(in_var in varchar2, in_format in varchar2)
return varchar2
is
  v_date date;
begin
  select to_date(in_var, in_format) into v_date from dual;
  return 'Y'; -- No exception, so is a number
exception
  when others then
  return 'N'; -- is not a number
end;

Below are two samples on how to use our newly created is_number() function.

select schema1.is_number('pete') from dual;
select schema1.is_number(123) from dual;

The first query will result "N" because "pete" is not a number. The second query will return "Y".

Below are four samples on how to use our newly created is_date() function.

select schema1.is_date('pete','mm/dd/yyyy') from dual;
select schema1.is_date('2-MAY-2008','mm/dd/yyyy') from dual;
select schema1.is_date('05/99/2008','mm/dd/yyyy') from dual;
select schema1.is_date('05/02/2008','mm/dd/yyyy') from dual;

The first query will result "N" because "pete" is not a date. The second also returns "N" because the date entered does not conform to the format specified. The third also will return "N" because the date, although formatted correctly, is not a valid date. Finally, we will have "Y" for the last test.

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 2 May 2008

bottom corner