Re: VARCHAR to NUMBER

From: Gollum <nospam_at_worldonline.dk>
Date: Mon, 5 Feb 2001 19:30:25 +0100
Message-ID: <YlCf6.23724$fa3.1343647@news010.worldonline.dk>

You will probably need to write your own function, eg.:

    create or replace function getnumber (str in varchar2) return number is

        str2 varchar2(20); -- Set size of str2 as required     begin

        str2 := '';
        for i in 1..length(str) loop
            if ascii(substr(str,i,1)) between 48 and 57 then
                str2 := str2 || substr(str,i,1);
            end if;
        end loop;
        return to number(str2);

    end;
    /

Then you can:

    select getnumber('X-123b') from dual;

which will return the number 123. Note that with the above function, the following query will also return 123:

    select getnumber('X1-Y2+Z3') from dual;

but it might give you an idea for a solution anyway.

HTH,
Gollum

"Marc Ottone" <marc.ottone_at_cjb.ville-ge.ch> wrote in message news:3a7eeec8_at_news.unige.ch...
> Hi,
>
> In order to select data (block based on table) I need to get NUMERIC
 value
> from VARCHAR
> like 'X -123b' = 123
> and use criteria like >100 and <200
> What would be the simple way to do this?
> TIA,
>
> Marc
>
> PS:
> TO NUMBER generate -01722: Invalid number error
>
>
> Marc Ottone
> Conservatoire et jardin botaniques de Gen ve
> marc.ottone_at_cjb.ville-ge.ch
>
>
>

--

Received on Mon Feb 05 2001 - 12:30:25 CST