Re: how to convert a string into a number?

From: jo <jose.soares_at_sferacarta.com>
Date: Sat, 06 Oct 2012 08:12:26 +0200
Message-ID: <506FCBCA.40002_at_sferacarta.com>

Well, my goal was to find the highest numeric value, skipping values not numeric, thus regexp_like, is exactly what I was looking for, as in:

SELECT MAX(CAST(t AS INTEGER)) AS max_number FROM test WHERE REGEXP_LIKE(t, '^[[:digit:]]+$')

max_number

---

123
(1 rows)

Thanks again to everybody.

Martin Berger wrote:
> what's about
>
> CREATE OR replace FUNCTION *To_number_only* (in_num /VARCHAR2/)
> RETURN /NUMBER/
> IS
> vosuser user_users.username%/TYPE/;
> ret_number /NUMBER/;
> ecode /NUMBER/(38);
> BEGIN
> ret_number := *To_number*(in_num);
>
> RETURN ret_number;
> EXCEPTION
> WHEN OTHERS THEN
> ecode := SQLCODE;
>
> dbms_output.*Put_line*('to_number_only - '
> || ecode);
>
> RETURN NULL;
> END to_number_only;
> /
>
> WITH test
> AS (SELECT '123' AS t
> FROM dual
> UNION
> SELECT '3' AS t
> FROM dual
> UNION
> SELECT '43' AS t
> FROM dual
> UNION
> SELECT 'ABC' AS t
> FROM dual)
> SELECT t,
> *To_number_only*(t) tno
> FROM test;
>
>
> On Fri, Oct 5, 2012 at 11:43 AM, Phillip Jones <phil_at_phillip.im
> <mailto:phil_at_phillip.im>> wrote:
>
> You don't state what you would like to happen when the number
> isn't numeric, so I'll just ignore the strings.
> There's a million and one ways to do this, but it's easy with a
> regexp:
>
> SQL> with test as
> (
> select '123' as t from dual
> union
> select '3' as t from dual
> union
> select '43' as t from dual
> union
> select 'ABC' as t from dual
> )
> select to_number(t)
> from test
> where regexp_like(t,'^[[:digit:]]+$');
>
> TO_NUMBER(T)
> ------------
> 123
> 3
> 43
>
> SQL>
>
> Phil
>
> On Fri, Oct 5, 2012 at 10:31 AM, jose soares
> <jose.soares_at_sferacarta.com <mailto:jose.soares_at_sferacarta.com>>wrote:
>
> > Hi all,
> >
> > I would like to convert
> > values of a varchar2 column type to a number.
> > I'm using to_number function
> > but I have mixed values in my table,
> > there are some columns with not numeric values
> > and there are some others with numbers as in:
> >
> > select * from test;
> >
> > text
> > --------
> > 123
> > 3
> > 43
> > ABC
> >
> >
>

-- 
Jose Soares Da Silva                     _/_/
Sferacarta Net
Via Bazzanese 69                       _/_/    _/_/_/
40033 Casalecchio di Reno             _/_/  _/_/  _/_/
Bologna - Italy                      _/_/  _/_/  _/_/
Ph  +39051591054              _/_/  _/_/  _/_/  _/_/
fax +390516131537            _/_/  _/_/  _/_/  _/_/
web:www.sferacarta.com        _/_/_/      _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l’integrità e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you.

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

Received on Sat Oct 06 2012 - 08:12:26 CEST