Home » SQL & PL/SQL » SQL & PL/SQL » convert char to number (Oracle 12.1.0.2.0)
convert char to number [message #683790] Mon, 22 February 2021 02:55 Go to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
Hello

I would like to ask how to make a select to convert char to number ?
I've got records like:
-90.900000000000
0.000000000000
-87.760000000000
133.350000000000
241.620000000000
I try to use any kind of to_number but not successfully.

Does anybody know how to do it right ??

Thanks

Regards
mape

[Updated on: Mon, 22 February 2021 03:07]

Report message to a moderator

Re: convert char to number [message #683791 is a reply to message #683790] Mon, 22 February 2021 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any kind? And the simplest one?
SQL> with data as (
  2    select '-90.900000000000' data from dual union all
  3    select '0.000000000000' data from dual union all
  4    select '-87.760000000000' data from dual union all
  5    select '133.350000000000' data from dual union all
  6    select '241.62000000000' data from dual
  7  )
  8  select to_number(data) from data
  9  /
TO_NUMBER(DATA)
---------------
          -90.9
              0
         -87.76
         133.35
         241.62

5 rows selected.
Re: convert char to number [message #683792 is a reply to message #683791] Mon, 22 February 2021 04:12 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member

The simeplest one gets me error: SQL error "ORA-01722: invalid number"
Re: convert char to number [message #683793 is a reply to message #683792] Mon, 22 February 2021 04:14 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
When I look at the the record in Hex, for instance 155.280000000000 , I see : 1.5.5...2.8.0.0.0.0.0.0.0.0.0.0.
Re: convert char to number [message #683794 is a reply to message #683793] Mon, 22 February 2021 05:13 Go to previous message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you don't post the actual issue you can't have an accurate answer.
Just remove the "." which are, I bet, "chr(0)" before applying to_number.

Previous Topic: Space is replaced by xA0 in SQL Query
Next Topic: Duplicate Records in output file
Goto Forum:
  


Current Time: Tue Apr 13 17:54:10 CDT 2021