OraFAQ Forum: SQL & PL/SQL » append leading zero's to data already in columns

Home » SQL & PL/SQL » SQL & PL/SQL » append leading zero's to data already in columns

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

append leading zero's to data already in columns [message #37682]

Wed, 20 February 2002 11:51

Toyn
Messages: 36
Registered: April 2001

Member

Hi,
I have numerical data in columns that needs to be 8 digits in length. I entered some of this data incorrectly as '123456', when it should have been '00123456'. Some of the data is not incorrect however (i.e.'00001234'). How can I update this data left padding it with the leading zero's? Is there a way I can select '123456', and update it to '00123456', while maintaining that each column remains 8 digits in length? Thanks.

Report message to a moderator

Re: append leading zero's to data already in columns [message #37683 is a reply to message #37682]

Wed, 20 February 2002 12:16

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

update t
   set c = lpad(c, 8, '0')
 where c <> lpad(c, 8, '0');

The WHERE clause could be dropped here if you don't mind updating every row (even those already formatted correctly).

Report message to a moderator

Previous Topic:	Re: sql query
Next Topic:	Apostrophe in Sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 10:50:58 CDT 2024