SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 add leading 0 in the field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dbonneau
Yak Posting Veteran

50 Posts

Posted - 11/11/2012 :  05:02:20  Show Profile  Reply with Quote
Hi,
I have zipcode field in the table.
But some of value in the field only has 4 digits instead of 5 digits for zipcode. So, I need to add leading 0 in the value if the value only has 4 digits. (eg. 3455 -> 03455), but if the value has 5 digits, it remains same (eg. 90210 -> 90210)
Could you tell me how to do that for sql statement ?

Thank you

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/11/2012 :  07:17:39  Show Profile  Reply with Quote
You will have to convert the data to a varchar type to preserve the leading zeros. For example like this, where the result is of type varchar
right('0'+cast(zipcode as varchar(5)),5)
Go to Top of Page

dbonneau
Yak Posting Veteran

50 Posts

Posted - 11/11/2012 :  17:41:35  Show Profile  Reply with Quote
quote:
Originally posted by sunitabeck

You will have to convert the data to a varchar type to preserve the leading zeros. For example like this, where the result is of type varchar
right('0'+cast(zipcode as varchar(5)),5)




Hi, Thank you so much for the syntax for my question.
I typed like following to add 0 in the zipcode field and it worked fine.
select right('0'+cast(zipcode as varchar(5),5) from my_table


But I am trying to update above value in my current table called my_table.
So, I wrote following syntax. But the value with 4 digits in zipcode field still showing as without 0 in the value.
update my_table set zipcode = right('0'+ cast(zipcode as varchar(5)),5)
select zipcode from my_table

Could you tell me how to replace new value with the value in my original table ?

Thank you
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 11/11/2012 :  19:10:49  Show Profile  Reply with Quote
If the datatype for the zip column is numeric, then nothing will work for saving the data with a leading zero. Zip code column should be a character based datatype instead of any type of numeric datatype.


--Jeff Moden
Go to Top of Page

dbonneau
Yak Posting Veteran

50 Posts

Posted - 11/11/2012 :  20:10:50  Show Profile  Reply with Quote
quote:
Originally posted by Jeff Moden

If the datatype for the zip column is numeric, then nothing will work for saving the data with a leading zero. Zip code column should be a character based datatype instead of any type of numeric datatype.


--Jeff Moden



Hi,

update my_table set zipcode = right('0'+ cast(zipcode as varchar(5)),5)

After i ran above syntax, I ran following logic
update my_table set zipcode = convert(char, zipcode);

but it still doesn't show leading 0.

Could you tell me how convert my zipcode as character based so it can show leading 0 ?

Thank you

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 11/12/2012 :  00:09:56  Show Profile  Reply with Quote
One thing you can do,

Case-1:

ALTER your table to add another column like as follows:

create table num_table(id int not null primary key identity(1,1),
num int, leading_zeros smallint,
constraint chk_leading_zero_nonnegative check (leading_zeros>=0));

ALTER TABLE num_table ADD num_formatted as replicate('0',coalesce(leading_zeros,0)) +cast(num as varchar(10));

insert into num_table(num,leading_zeros) values(23,2) ;
select num_formatted from num_table; -- output '0023'


Case-2:

ALTER Your Column datatype to VARCHAR
Syntax:
alter table YourTableName alter column ColumnName varchar(10)

--
Chandu

Edited by - bandi on 11/12/2012 00:23:22
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 11/12/2012 :  12:43:32  Show Profile  Reply with Quote
quote:
Originally posted by dbonneau

quote:
Originally posted by Jeff Moden

If the datatype for the zip column is numeric, then nothing will work for saving the data with a leading zero. Zip code column should be a character based datatype instead of any type of numeric datatype.


--Jeff Moden



Hi,

update my_table set zipcode = right('0'+ cast(zipcode as varchar(5)),5)

After i ran above syntax, I ran following logic
update my_table set zipcode = convert(char, zipcode);

but it still doesn't show leading 0.

Could you tell me how convert my zipcode as character based so it can show leading 0 ?

Thank you





I could tell you but then you wouldn't learn as much. My recommendation is to check "ALTER TABLE" in Books Online for how to do this (examples at the end are really helpful). "Books Online" is the free help system that comes with SQL Server. Learning how to find stuff in it is critical to career success with SQL Server.


--Jeff Moden
Go to Top of Page

dbonneau
Yak Posting Veteran

50 Posts

Posted - 11/12/2012 :  15:33:07  Show Profile  Reply with Quote
quote:
I could tell you but then you wouldn't learn as much. My recommendation is to check "ALTER TABLE" in Books Online for how to do this (examples at the end are really helpful). "Books Online" is the free help system that comes with SQL Server. Learning how to find stuff in it is critical to career success with SQL Server.

--Jeff Moden



Hi Jeff, Do you mean booksonline.com ? I couldn't find anything regarding sql in this website..

Thank you.
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 11/13/2012 :  00:58:53  Show Profile  Reply with Quote
quote:
Originally posted by dbonneau

quote:
I could tell you but then you wouldn't learn as much. My recommendation is to check "ALTER TABLE" in Books Online for how to do this (examples at the end are really helpful). "Books Online" is the free help system that comes with SQL Server. Learning how to find stuff in it is critical to career success with SQL Server.

--Jeff Moden



Hi Jeff, Do you mean booksonline.com ? I couldn't find anything regarding sql in this website..

Thank you.



No. If you right click in the menu area of SSMS, one of the toolbars you can select is "Help". THAT's Books Online.


--Jeff Moden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 11/17/2012 :  03:31:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
or press F1 which will by default open help file

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000