OraFAQ Forum: SQL & PL/SQL » how to convert varchar2 to number

Home » SQL & PL/SQL » SQL & PL/SQL » how to convert varchar2 to number

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

how to convert varchar2 to number [message #574702]

Tue, 15 January 2013 06:03

anniepeteroracle
Messages: 38
Registered: March 2012
Location: hyderabad

Member

How to convert varchar2 to number data?

Annie

Report message to a moderator

Re: how to convert varchar2 to number [message #574703 is a reply to message #574702]

Tue, 15 January 2013 06:07

Michel Cadot
Messages: 54702
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Use TO_NUMBER (with a format mask).

Maybe you should read the Database SQL Reference.

Regards
Michel

[Updated on: Tue, 15 January 2013 06:07]

Report message to a moderator

Re: how to convert varchar2 to number [message #574704 is a reply to message #574703]

Tue, 15 January 2013 06:17

anniepeteroracle
Messages: 38
Registered: March 2012
Location: hyderabad

Member

I have the data like x25628 and i need to change it with number,and again when i need the same data then i have to convert that number to get the same data like x25628---> certain number ----> x25628

Annie

Report message to a moderator

Re: how to convert varchar2 to number [message #574705 is a reply to message #574704]

Tue, 15 January 2013 06:25

msol25
Messages: 214
Registered: June 2011
Location: Mumbai

Senior Member

Storing as number :

select to_number(ascii(substr(sno,1,1))||substr(sno,2)) 
from   table;

Storing as VARCHAR2:

select chr(ascii(substr(sno,1,1)))||substr(sno,2) 
from table;

Report message to a moderator

Re: how to convert varchar2 to number [message #574707 is a reply to message #574704]

Tue, 15 January 2013 06:29

Michel Cadot
Messages: 54702
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Which number is x25628? For me it is a string not a number.
You have to better explain your requirements if you want some help.

Regards
Michel

Report message to a moderator

Re: how to convert varchar2 to number [message #574709 is a reply to message #574705]

Tue, 15 January 2013 06:30

anniepeteroracle
Messages: 38
Registered: March 2012
Location: hyderabad

Member

the data is like
xa24534
32545x65f767

in this case this conversion will not going to work .so what is the solution for this type of data...

thanks for the above solution

Annie

Report message to a moderator

Re: how to convert varchar2 to number [message #574710 is a reply to message #574705]

Tue, 15 January 2013 06:30

Michel Cadot
Messages: 54702
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

@msol2R5,

This is a silly answer.
How could you provide an answer with no specification?
But I forget you are a specialist of this kind of answers; you don't know what is asked but you can give a "solution".

Regards
Michel

Report message to a moderator

Re: how to convert varchar2 to number [message #574711 is a reply to message #574709]

Tue, 15 January 2013 06:33

anniepeteroracle
Messages: 38
Registered: March 2012
Location: hyderabad

Member

the data is in varcha2('xa24534','x25628','32545x65f767')

Annie

Report message to a moderator

Re: how to convert varchar2 to number [message #574712 is a reply to message #574710]

Tue, 15 January 2013 06:34

msol25
Messages: 214
Registered: June 2011
Location: Mumbai

Senior Member

Dear Michel,

Yes i agree with you,I hope he is stuck somewhere,so he is expecting.....But, as i also know question is wrong...

Dear anniepeteroracle ,

you should read the Database SQL Reference AS said by Michel

[Updated on: Tue, 15 January 2013 06:35]

Report message to a moderator

Re: how to convert varchar2 to number [message #574713 is a reply to message #574709]

Tue, 15 January 2013 06:37

Solomon Yakobson
Messages: 1442
Registered: January 2010

Senior Member

anniepeteroracle wrote on Tue, 15 January 2013 07:30

so what is the solution for this type of data...

What type of data? What does X represent in your string? What result are you expecting from xa24534 and from
32545x65f767? And what is the logic to get such results? Learn how to ask a question. Put yourself into answerer's shoes and think did I provide all that's needed to provide solution.

SY.

Report message to a moderator

Previous Topic:	update statement help
Next Topic:	dates question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 19 07:59:52 CDT 2013

Total time taken to generate the page: 0.10687 seconds