{"id":69,"date":"2008-05-02T15:45:08","date_gmt":"2008-05-02T19:45:08","guid":{"rendered":"http:\/\/www.dev-notes.com\/blog\/2008\/05\/02\/custom-is_number-and-is_date-functions-in-oracle\/"},"modified":"2008-05-02T15:45:08","modified_gmt":"2008-05-02T19:45:08","slug":"custom-is_number-and-is_date-functions-in-oracle","status":"publish","type":"post","link":"https:\/\/www.dev-notes.com\/blog\/2008\/05\/02\/custom-is_number-and-is_date-functions-in-oracle\/","title":{"rendered":"Custom is_number() and is_date() functions in Oracle"},"content":{"rendered":"

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.<\/p>\n

\ncreate or replace function schema1.is_number(in_var in varchar2)\nreturn varchar2\nis\n  v_number number;\nbegin\n  select to_number(in_var) into v_number from dual;\n  return 'Y'; -- No exception, so is a number\nexception\n  when others then\n  return 'N'; -- is not a number\nend;\n\ncreate or replace function schema1.is_date(in_var in varchar2, in_format in varchar2)\nreturn varchar2\nis\n  v_date date;\nbegin\n  select to_date(in_var, in_format) into v_date from dual;\n  return 'Y'; -- No exception, so is a number\nexception\n  when others then\n  return 'N'; -- is not a number\nend;\n<\/pre>\n
Below are two samples on how to use our newly created is_number() function.<\/p>\n
\nselect schema1.is_number('pete') from dual;\nselect schema1.is_number(123) from dual;\n<\/pre>\n
The first query will result “N” because “pete” is not a number.  The second query will return “Y”.<\/p>\n
Below are four samples on how to use our newly created is_date() function.<\/p>\n
\nselect schema1.is_date('pete','mm\/dd\/yyyy') from dual;\nselect schema1.is_date('2-MAY-2008','mm\/dd\/yyyy') from dual;\nselect schema1.is_date('05\/99\/2008','mm\/dd\/yyyy') from dual;\nselect schema1.is_date('05\/02\/2008','mm\/dd\/yyyy') from dual;\n<\/pre>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-69","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/69","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=69"}],"version-history":[{"count":0,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/69\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=69"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=69"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=69"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}