August 2, 2007 at 12:57 pm
I have a zip code field that I imported from an excel spreadsheet.
When I imported it into the database I realized I was missing the 'leading' 0 for
all the east coast states. So they only have 4 instead of the 5 numbers.
How would I go about updating the zipcode field to add a 0 to the beginning of those with only 4 numbers?
August 2, 2007 at 1:11 pm
August 2, 2007 at 1:26 pm
What datatype are you using? If INT, your out of luck as you cannot append a zero to the front end of an integer, SQL Server stores 01234 as 1234 for integers. You could handle this at the presentation layer. If you are using some sort of string datatype, this will work:
DECLARE @Zips TABLE (ZipCode varchar(10))
SET NOCOUNT ON
INSERT INTO @Zips
SELECT '12345' UNION ALL
SELECT '1234'
SELECT * FROM @Zips
UPDATE @Zips
SET ZipCode = '0' + ZipCode
WHERE LEN(ZipCode) = 4
SELECT * FROM @Zips
August 2, 2007 at 1:28 pm
Thanks that worked!!
August 3, 2007 at 3:39 am
And a more general version, handy when you need to achieve given length, but have to add variable number of leading zeroes to do it.
DECLARE @Zips TABLE (ZipCode varchar(10))
SET NOCOUNT ON
INSERT INTO @Zips
SELECT '12345' UNION ALL
SELECT '1234' UNION ALL
SELECT '123' UNION ALL
SELECT '1'
SELECT * FROM @Zips
UPDATE @Zips
SET ZipCode = RIGHT('00000' + ZipCode,5)
WHERE LEN(ZipCode) < 5
SELECT * FROM @Zips
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply