Home » SQL & PL/SQL » SQL & PL/SQL » how to generate number without exceed 6 digigts
how to generate number without exceed 6 digigts [message #362586] Wed, 03 December 2008 02:26 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
I have genearet number like this how to increment with left padding like this;it should not exceed 6 digits;

901001
901002
901003
901004
901005
901006
901007
901008
901009
901010
.
.
901099
901100
Re: how to generate number without exceed 6 digigts [message #362587 is a reply to message #362586] Wed, 03 December 2008 02:38 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
select lpad(rownum,6,'0') from dual connect by rownum <= 120
Re: how to generate number without exceed 6 digigts [message #362588 is a reply to message #362586] Wed, 03 December 2008 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TO_CHAR

Regards
Michel
Re: how to generate number without exceed 6 digigts [message #362589 is a reply to message #362587] Wed, 03 December 2008 02:55 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
panyam wrote on Wed, 03 December 2008 02:38
select lpad(rownum,6,'0') from dual connect by rownum <= 120


This does not take into consideration the "901" at the beginning.


Try this:
select 901000 + rownum from dual connect by rownum <= 100 


Rajy
Re: how to generate number without exceed 6 digigts [message #362591 is a reply to message #362586] Wed, 03 December 2008 03:02 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
i want to concatenate some string before that number but below one is not wokring;

select 'AC07/901000' || rownum from dual connect by rownum <= 100
Re: how to generate number without exceed 6 digigts [message #362595 is a reply to message #362591] Wed, 03 December 2008 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you read and explain what you posted?

Regards
Michel
Re: how to generate number without exceed 6 digigts [message #362596 is a reply to message #362586] Wed, 03 December 2008 03:06 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
actually i want like this

AC07/901001
AC07/901002
AC07/901003
AC07/901004
AC07/901005
AC07/901006
AC07/901007
AC07/901008
AC07/901009
AC07/901010
.
.
AC07/901099
AC07/901100
Re: how to generate number without exceed 6 digigts [message #362599 is a reply to message #362591] Wed, 03 December 2008 03:12 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

keep 'AC07/901' fix and DO lpad remaining number. LIKE

SELECT 'AC07/901'||TO_CHAR(LPAD(ROWNUM,5,'0')) FROM all_tab_columns;
Re: how to generate number without exceed 6 digigts [message #362604 is a reply to message #362599] Wed, 03 December 2008 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need LPAD if you correctly use TO_CHAR.

More TO_CHAR on a string is... inappropriate.

Regards
Michel

[Updated on: Wed, 03 December 2008 03:44]

Report message to a moderator

Re: how to generate number without exceed 6 digigts [message #362606 is a reply to message #362604] Wed, 03 December 2008 03:49 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

I do agree. I did wrong use of TO_CHAR on a string.

but i am not getting
Quote:
You don't need LPAD if you correctly use TO_CHAR.

how to_char will work as lpad functionality.
Michel, can you please explain.?
Re: how to generate number without exceed 6 digigts [message #362607 is a reply to message #362606] Wed, 03 December 2008 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_char(1,'fm000000') from dual;
TO_CHAR
-------
000001

1 row selected.

Regards
Michel
Re: how to generate number without exceed 6 digigts [message #362608 is a reply to message #362607] Wed, 03 December 2008 03:53 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

WOW, To be very frank i am not aware about this.
Thanks Michel.
Re: how to generate number without exceed 6 digigts [message #362612 is a reply to message #362608] Wed, 03 December 2008 04:04 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To learn more, just click on the link I provided in my first answer and follow "Format models".

Regards
Michel
Previous Topic: How to read the blob column and write into file? (merged)
Next Topic: LONG value VS DB Links
Goto Forum:
  


Current Time: Wed Dec 07 20:44:37 CST 2016

Total time taken to generate the page: 0.11144 seconds