Re: SQL Help

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/06/05
Message-ID: <33968916.10825109@www.sigov.si>#1/1

On Wed, 04 Jun 1997 14:13:15 -0400, "Lawrence J. Rizzo" <lrizzo_at_pce.net> wrote:

>Hello,
>
>I am having trouble in Oracle 7.3 SQL. I have a VarChar2 column that
>sometimes contains numeric information and other times string. I need
>to query the column for numeric values in a given range. I cannot seem
>to find a function / method to allow me to do something like:
>
>SELECT EDI_PO_NUMBER
>FROM EDI
>WHERE VAL(EDI_PO_NUMBER) BETWEEN 102 AND 119;
>
>I know VAL is not a valid Oracle function but if the BASIC equivalent of
>VAL was available it should work.
>
>I did try using TO_NUMBER but no luck as I get a run time query data
>mismatch error.
>
>Any ideas?

You can very easily create your own VAL function with PL/SQL and then call it from SQL.

Something like:

CREATE OR REPLACE FUNCTION val(p_string IN VARCHAR2) RETURN NUMBER IS
  v_num NUMBER;
BEGIN
  v_num := TO_NUMBER(p_string);
  RETURN v_num;
EXCEPTION
  WHEN OTHERS THEN RETURN NULL;
END;
/

Now your select statement will return records you are looking for.

Regards,

---

Jurij Modic                             Republic of Slovenia
tel: +386 61 178 55 14                  Ministry of Finance
fax: +386 61  21 45 84                  Zupanciceva 3

e-mail: jurij.modic_at_mf.sigov.mail.si Ljubljana 1000

---

Received on Thu Jun 05 1997 - 00:00:00 CDT