OraFAQ Forum: SQL & PL/SQL » string to number conversion

Home » SQL & PL/SQL » SQL & PL/SQL » string to number conversion (oracle 11g)

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

string to number conversion [message #610324]

Wed, 19 March 2014 07:31

meeplsql
Messages: 5
Registered: February 2014
Location: bangalore

Junior Member

select to_number('01407027','99999999') from dual

output is 1407027

but
I want output as 01407027

thanks in adv.

Report message to a moderator

Re: string to number conversion [message #610326 is a reply to message #610324]

Wed, 19 March 2014 07:33

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Then don't to_number it. Numbers do not have leading zeros, ever.

Report message to a moderator

Re: string to number conversion [message #610329 is a reply to message #610326]

Wed, 19 March 2014 07:39

meeplsql
Messages: 5
Registered: February 2014
Location: bangalore

Junior Member

actualy that data stored as varchar wanted to convert to number.

this is the work assigned as training part

Report message to a moderator

Re: string to number conversion [message #610331 is a reply to message #610329]

Wed, 19 March 2014 07:44

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

that doesn't change the fact that numbers don't have leading zeros.
If you want to display a number with leading zeros you have to_char it.
If it already is a char you should leave it as is.

Report message to a moderator

Re: string to number conversion [message #610334 is a reply to message #610324]

Wed, 19 March 2014 07:58

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

select LPAD( to_number('01407027',99999999) , 8, '0') from dual

o/p:
---
01407027

[Updated on: Wed, 19 March 2014 07:59]

Report message to a moderator

Re: string to number conversion [message #610336 is a reply to message #610334]

Wed, 19 March 2014 08:09

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

lpad implicitly converts numbers to chars.

Report message to a moderator

Re: string to number conversion [message #610338 is a reply to message #610336]

Wed, 19 March 2014 08:13

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Quote:

lpad implicitly converts numbers to chars.

Yes , padding with Zeros.. Smile

if i did any wrong so please let you correct.

Report message to a moderator

Re: string to number conversion [message #610339 is a reply to message #610338]

Wed, 19 March 2014 08:17

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

Senior Member
Account Moderator

What is wrong is that you apply a string function on a number.
What is wrong is that you rely on implicit conversion.
2 bad points.

Report message to a moderator

Re: string to number conversion [message #610341 is a reply to message #610339]

Wed, 19 March 2014 08:25

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Quote:

What is wrong is that you apply a string function on a number.
What is wrong is that you rely on implicit conversion.
2 bad points.

LPAD function pads the left-side of a string with a specific set of characters right? That's
why i thought that just add the lpad. If there any another way so please let me know.

select LPAD('mist', 8, '0') from dual	would return '0000mist'

Report message to a moderator

Re: string to number conversion [message #610343 is a reply to message #610341]

Wed, 19 March 2014 08:32

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Generally the correct way to add leading zeros to a number is to use to_char with the correct format mask.

The correct approach in this particular case is to do nothing.
The value is a char with leading zeros to start with. using to_number and lpad just converts it to something else and then back to what it was in the first place, and so is a complete waste of time.

Report message to a moderator

Re: string to number conversion [message #610348 is a reply to message #610341]

Wed, 19 March 2014 08:44

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

Senior Member
Account Moderator

Quote:

LPAD function pads the left-side of a string with a specific set of characters right?

Right but in your previous case the parameter is NOT a string it is a NUMBER.
(Without speaking about cookiemonster's answer which addresses the core of the issue when I just addresses the form).

Report message to a moderator

Previous Topic:	Difference between share & Exclusive Locks
Next Topic:	Data validation in staging table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 20:09:41 CDT 2024