Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Testing for datatype in columns?

Re: Testing for datatype in columns?

From: Daniel Clamage <dclamageNOSPAM_at_telerama.com>
Date: 17 Jul 1998 00:24:00 -0400
Message-ID: <01bdb10f$ec99f360$cf28c9cd@saturn>


If you attempt to convert the strings to number or date (as appropriate) in a PL/SQL block, you can use the exception handler to trap the invalid conversion.
CREATE OR REPLACE FUNCTION to_num(Pstr IN VARCHAR2) RETURN NUMBER IS
  num NUMBER;
BEGIN
  num := TO_NUMBER(Pstr); -- attempt conversion   RETURN(num); -- converted OK
EXCEPTION
WHEN OTHERS THEN
  RETURN(NULL); -- failed conversion
END is_number;

Then use this stored function in SQL:
select doc_num, revision
from document
where is_num(NVL(revision,'0')) IS NOT NULL;

If you know revision can never be NULL, you can drop the NVL function. --
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.

rune.slinning_at_bigfoot.com wrote in article <6om9sd$cm6$1_at_nnrp1.dejanews.com>...
> Hi All!
>
> I have a table DOCUMENT with the following columns:
>
> doc_no varchar2(30),
> revision varchar2(30),
> issue_date varchar2(30)
>
> I need a query to check if the content of the revision column is numeric
and
> the content of the issue_date is a date.
>
> I would need a query with similar functionality as the ones below:
>
> select doc_num, revision
> from document
> where revision is not numeric;
>
> select doc_num, issue_date
> from document
> where issue_date is not date;
>
> Thanks,
> Rune
>
>
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>
Received on Thu Jul 16 1998 - 23:24:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US