Re: having a senior moment and can't fingure

From: tomi wijanto <restomi_w_at_yahoo.com>
Date: Tue, 26 Oct 2004 22:38:26 -0700 (PDT)
Message-ID: <20041027053826.31932.qmail@web52001.mail.yahoo.com>

Hi,

your query can be like this:

select field1 from table1 where length(field1) = 4 and is_number(field1) = 1;

while is_number is seperate function like this:

• return 1 if input param can be converted to number
• return 0 otherwise create function is_number (p_num in varchar2) return number is n_result pls_integer; begin

  declare
    n_num_temp number;
  begin
    n_num_temp := to_number(p_num);
    n_result := 1;
  exception
    when others then
      n_result := 0;
  end;

  return n_result;
end is_number;
/

regards,
tomi

• "Duret, Kathy" <kduret_at_starkinvestments.com> wrote:

> I am having a senior moment today .....
>
> sI want to get only those varchar2 fields back with
> a length of 4 that are
> numeric
>
> select field from table where length(field) = 4 and
>
> between '0001' and '9999' - I used this but I get
> alpha characters back.
>
> I know it is something simple but I am just going
> blank.
>
> Kathy
                

---

Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 27 2004 - 00:33:56 CDT