Home » SQL & PL/SQL » SQL & PL/SQL » Display single digit numbers in double digits (Merged)
Display single digit numbers in double digits (Merged) [message #265026] Wed, 05 September 2007 05:06 Go to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi


I have small problem.loc column name and it contain single digit values example:
Loc : 0
1
2
3
4 .........9,from 10 to remaing show normal values.
user want to see double digit values like
00
01
02
03
04
........09,which function i want to use?

Re: single digit codes into doble digit codes? [message #265027 is a reply to message #265026] Wed, 05 September 2007 05:09 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

USE LPAD function.

For Example:
select decode(length('&val'),1,lpad('&val',2,'0'),'&val') from dual;


Cheers
Soumen

[Updated on: Wed, 05 September 2007 05:13]

Report message to a moderator

Re: single digit codes into doble digit codes? [message #265032 is a reply to message #265027] Wed, 05 September 2007 05:16 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
i cant understand,how we use lpad for this?i want to change 0 to 9 and remaing are same?
Re: single digit codes into doble digit codes? [message #265034 is a reply to message #265032] Wed, 05 September 2007 05:18 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

tondapi wrote on Wed, 05 September 2007 11:16
i cant understand,how we use lpad for this?i want to change 0 to 9 and remaing are same?


Please read the reply as I've posted the example also.

Cheers
Soumen
Re: single digit codes into doble digit codes? [message #265036 is a reply to message #265027] Wed, 05 September 2007 05:19 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

or use the to_char function.

select to_char(col,'09') from dual



regards,
converting numbers? [message #265084 is a reply to message #265026] Wed, 05 September 2007 06:50 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi

I want to convert 0,1,2 as 00,01,02

what function i want to use?i cant get by using lpad?
Re: converting numbers? [message #265089 is a reply to message #265084] Wed, 05 September 2007 07:05 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

why do you create new thread for similar questions?did you try the solutions given to you.

use to_char(col,'09')




regards,

[Updated on: Wed, 05 September 2007 07:06]

Report message to a moderator

Re: converting numbers? [message #265091 is a reply to message #265089] Wed, 05 September 2007 07:13 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
it's not working?


select to_char(loc,'09') from emp;
Re: converting numbers? [message #265092 is a reply to message #265089] Wed, 05 September 2007 07:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Merged the two posts.
Re: Display single digit numbers in double digits (Merged) [message #265098 is a reply to message #265026] Wed, 05 September 2007 07:26 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
What is the datatype of loc. For see datatype write the following command
desc emp
Re: Display single digit numbers in double digits (Merged) [message #265134 is a reply to message #265098] Wed, 05 September 2007 08:55 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Datatype is number.

[Updated on: Wed, 05 September 2007 08:55]

Report message to a moderator

Re: Display single digit numbers in double digits (Merged) [message #265140 is a reply to message #265134] Wed, 05 September 2007 09:21 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Why don't you try to use
select decode(length('&val'),1,lpad('&val',2,'0'),'&val') from dual;


here '&val' is a variable and query will return 01 if you entered 1 and 19 if you entered 19. Hope this is the requirment.
Arrange the query by replacing '&val' to your column name and dual by your table name.

Cheers
Soumen
Re: Display single digit numbers in double digits (Merged) [message #265142 is a reply to message #265140] Wed, 05 September 2007 09:28 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
select decode(length('&val'),1,lpad('&val',2,'0'),'&val') from dual;

In the place of &val i want to place number like 01,for example


select decode(length('01'),1,lpad('02',2,'0'0),'03')from dual;
Re: Display single digit numbers in double digits (Merged) [message #265151 is a reply to message #265142] Wed, 05 September 2007 09:56 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

No. You are wrong.
'&val' is single variable name and you can't put the multiple value for a single variable.

By the way, if you put 1 in this query it looks like
select decode(length('1'),1,lpad('1',2,'0'),'1') as result from dual;

and returns the result as
result
-------
01
[/code]
Hope this make sense.
Cheers
Soumen

[Updated on: Wed, 05 September 2007 09:57]

Report message to a moderator

Re: Display single digit numbers in double digits (Merged) [message #265180 is a reply to message #265151] Wed, 05 September 2007 12:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do it the easy way (to_char(n, '00')) when there is a hard way, right?
Re: Display single digit numbers in double digits (Merged) [message #265282 is a reply to message #265180] Thu, 06 September 2007 00:33 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
SQL*Plus allows you to set a numeric format:
SQL> set numformat "9999900"
SQL> select 1 from dual;

       1
--------
      01
Drawbacks:
it's only in SQL*Plus
it's for ALL numeric values

MHE
Previous Topic: Tracle PL/SQL code
Next Topic: Select
Goto Forum:
  


Current Time: Sat Dec 03 07:45:48 CST 2016

Total time taken to generate the page: 0.15929 seconds