Home » SQL & PL/SQL » SQL & PL/SQL » adding zeros to the left of a number in the number column
adding zeros to the left of a number in the number column [message #293034] Thu, 10 January 2008 11:57 Go to next message
arjunora
Messages: 1
Registered: January 2008
Junior Member
Hi,


I have a requirement to generate a sequence and the sequence should be in the following format "Must be a number starting from 00000001 and incremented by 1. This field must be unique in the interface."

If I make the column structure to varchar2 the sequence is not incrementing by 1 though I use the following query for creating sequence

CREATE SEQUENCE aaa_seq
START WITH 1 INCREMENT BY 1 CACHE 5;


so I changed the column to number data type but the zerps are not coming in the left. Please help

Regards
Arjun G

Re: adding zeros to the left of a number in the number column [message #293036 is a reply to message #293034] Thu, 10 January 2008 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use TO_CHAR to convert the sequence number in the format you want.
Sequence generates numbers, numbers do not have leading 0.

Regards
Michel
Re: adding zeros to the left of a number in the number column [message #293039 is a reply to message #293034] Thu, 10 January 2008 12:52 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Do something like


insert into my_table(key) values(to_char(myseq.nextval,'fm00000000'))
Re: adding zeros to the left of a number in the number column [message #293041 is a reply to message #293034] Thu, 10 January 2008 12:59 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>values(to_char(myseq.nextval,'fm00000000'))
Depending upon implicit datatype conversion is a "bad" implementation choice.
In other words, jamming a character string (TO_CHAR) into a NUMBER field should be avoided at all costs.
This advice is regardless of whether or not it produces the desired results (or not).
Re: adding zeros to the left of a number in the number column [message #293042 is a reply to message #293041] Thu, 10 January 2008 13:03 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
anacedent wrote on Thu, 10 January 2008 12:59

>values(to_char(myseq.nextval,'fm00000000'))
Depending upon implicit datatype conversion is a "bad" implementation choice.
In other words, jamming a character string (TO_CHAR) into a NUMBER field should be avoided at all costs.
This advice is regardless of whether or not it produces the desired results (or not).


I am not saying jam a character string into a number field, I am saying use the sequence to place a formatted value into a varchar2(8) field. This is what the OP wanted (unless I am mistaken)

[EDITED by LF: disabled smilies to avoid smiley at varchar2(8)]

[Updated on: Fri, 11 January 2008 01:15] by Moderator

Report message to a moderator

Previous Topic: BULK COLLECT
Next Topic: Hierarchy, sort of
Goto Forum:
  


Current Time: Sun Dec 11 00:05:17 CST 2016

Total time taken to generate the page: 0.27277 seconds