padding a field in a table - 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)

padding a field in a table

Author

Topic

texas1992
Starting Member

21 Posts

Posted - 2012-12-07 : 13:39:07

I have a table that had a field which was a int but I was asked to change it to a varchar(5).

I did this but now my client is requesting that all numbers be 5 chars long. This means that I have to pad any numbers that are 4 chars long with a zero.

i.e. 1234 will become '01234'

I am not a DBA by any means so I am asking how to do this? I am just updating one field but there may be multiple occurances of a value in the field.

The field that I want to update is A and the field B is a unique field.

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-07 : 13:46:03

Customer is almost always right, but this may be a case where they are not.

If the data is numeric and if it is currently stored in a column that is of numeric data type, keep it that way.

When you want to select, simply cast it to varchar(5) like shown below and return the results:

SELECT RIGHT('00000'+CAST(YourColumn AS VARCHAR(5)),5) FROM YourTable

Another possibility is to create a computed column.

ALTER TABLE YourTable ADD CharCol AS RIGHT('00000'+CAST(YourColumn AS VARCHAR(5)),5);

Then select Charcoal instead of Yourcolumn.

Edit: If you have already changed it to varchar(5) and just want to pad with zeros, do this

UPDATE YourTable SET YourColumn = RIGHT('00000'+CAST(YourColumn AS VARCHAR(5)),5);

texas1992
Starting Member

21 Posts

Posted - 2012-12-07 : 13:51:56

Thanks. Yes, I have already changed the field so your update statement worked great.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-12-24 : 06:45:20

This is the formation issue. Just do this while SELECTing data

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources