CODEWITHSUNDEEP
sql-server
Jason Matney
Jason Matney

Reputation: 552

Update zipcode to Pad with Zeros in SQL SERVER 2012

I have a table with a Zipcode column. The Zipcode column is an int. How to I update this column to show padded zeros such that all values are 5 digits? I know how to do this as a SELECT statement, but I don't know how to then update the coulmn in the table. Below is my best effort. 

USE RTCA_new
GO

UPDATE tbl_BASE
SET Zipcode = (
SELECT FORMAT(Zipcode, '00000')
FROM rtca.tbl_BASE
               )

The resulting error is:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

In short, I would like a zipcode value 802 to display as 00802

Upvotes: 1

Views: 5221

Answers (4)

shawnt00
shawnt00

Reputation: 17935

It's probably best to change the database design if possible (even though you might save a tiny bit of storage in the row by not using a string value.) If you do just need to convert it only for display purposes it's just a question of conversion.

select right('00000' + cast(zipcode as varchar(5)), 5) from T...
-- for use format() on later editions of SQL Server

By the way I would still recommend doing some reading about update to explore why you didn't really want a subquery for what you were trying to accomplish.

You could add a computed column to the table if you need this conversion to happen on the fly. Seeing that you were trying to update a column with a formatted value is probably evidence that the biggest problem was indeed a misunderstanding of data types as has already been pointed out.

Upvotes: 2

FutbolFan
FutbolFan

Reputation: 13743

You will need to change your datatype for the Zipcode column to varchar. Once you do that, you could use replicate, concat and right functions to format your data:

UPDATE tbl_BASE
SET zipcode = right(CONCAT(
            replicate('0', 5)
            ,cast(zipcode as varchar(5))
            ), 5)

Upvotes: 1

Kritner
Kritner

Reputation: 13765

As you've probably figured out, zip codes can start with 0. integers in sql server do not allow for 0 padding.

I see your options as:

  1. always padding it in the select
  2. changing the column type to a more appropriate varchar and padding that with 0s up to 5 characters. (you should do this preferably)

Steps for new columns:

  1. Create new varchar(5) column for zipCode (newZipCode as example)

  2. populate the newZipCode column as:

    update tbl_base set newZipCode = right('00000' + cast(zipCode as varchar(5)), 5)

  3. drop your int zipcode column

  4. rename your newZipCode column to zipCode

The specific reason for your error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

In the query:

UPDATE tbl_BASE
SET Zipcode = (
SELECT FORMAT(Zipcode, '00000')
FROM rtca.tbl_BASE
               )

Is because you have a logic error or similar in your statement. you're attempting to set a scalar value (ZipCode) to a set's value (your select format... statement.)

the statement:

SELECT FORMAT(Zipcode, '00000')
FROM rtca.tbl_BASE

by itself would return a row for every row in the table, you can't set a whole result set to a scalar value. The immediate error can be fixed as so:

UPDATE tbl_BASE
SET Zipcode = FORMAT(Zipcode, '00000')

this would return a single value for zipcode... though again due to your current table schema, this would not give you the result you're hoping as integers can't be padded with 0s

Upvotes: 7

Luc
Luc

Reputation: 1491

It works different:

Update tbl_BASE set zipcode = right('000000' + zipcode, 6)

Upvotes: 2

Related Questions