| Author |
 Topic  |
|
|
dbonneau
Starting Member
42 Posts |
 Posted - 11/11/2012 : 05:02:20
|
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
5152 Posts |
Posted - 11/11/2012 : 07:17:39
|
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 varcharright('0'+cast(zipcode as varchar(5)),5) |
 |
|
|
dbonneau
Starting Member
42 Posts |
Posted - 11/11/2012 : 17:41:35
|
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 varcharright('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 |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
644 Posts |
Posted - 11/11/2012 : 19:10:49
|
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 |
|
|
|
dbonneau
Starting Member
42 Posts |
Posted - 11/11/2012 : 20:10:50
|
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
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1702 Posts |
Posted - 11/12/2012 : 00:09:56
|
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 |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
644 Posts |
Posted - 11/12/2012 : 12:43:32
|
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 |
|
|
|
dbonneau
Starting Member
42 Posts |
Posted - 11/12/2012 : 15:33:07
|
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. |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
644 Posts |
Posted - 11/13/2012 : 00:58:53
|
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 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 11/17/2012 : 03:31:37
|
or press F1 which will by default open help file
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
add leading 0 in the field
