Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Testing for datatype in columns?
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
![]() |
![]() |