add leading 0 in the field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

add leading 0 in the field

Author

Topic

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2012-11-11 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-11 : 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 varchar

right('0'+cast(zipcode as varchar(5)),5)

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2012-11-11 : 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 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

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-11-11 : 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
Yak Posting Veteran

50 Posts

Posted - 2012-11-11 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-12 : 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

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-11-12 : 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
Yak Posting Veteran

50 Posts

Posted - 2012-11-12 : 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

652 Posts

Posted - 2012-11-13 : 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

22864 Posts

Posted - 2012-11-17 : 03:31:37

or press F1 which will by default open help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources