Re: VARCHAR to NUMBER

Thanks Gollum. You're right. I have to admit this must be the best and "simplest" way to do it.
With kind regards,
Marc
  "Gollum" <gollum nospam_at_worldonline.dk> a crit dans le message news: YlCf6.23724$fa3.1343647_at_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
>
>
>

--