OraFAQ Forum: SQL & PL/SQL » convert char to number

Home » SQL & PL/SQL » SQL & PL/SQL » convert char to number (Oracle 12.1.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

convert char to number [message #683790]

Mon, 22 February 2021 02:55

mape
Messages: 298
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

Michel Cadot
Messages: 68641
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.

Report message to a moderator

Re: convert char to number [message #683792 is a reply to message #683791]

Mon, 22 February 2021 04:12

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

The simeplest one gets me error: SQL error "ORA-01722: invalid number"

Report message to a moderator

Re: convert char to number [message #683793 is a reply to message #683792]

Mon, 22 February 2021 04:14

mape
Messages: 298
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.

Report message to a moderator

Re: convert char to number [message #683794 is a reply to message #683793]

Mon, 22 February 2021 05:13

Michel Cadot
Messages: 68641
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 17 23:36:19 CDT 2024