put a dash in zipcode - 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 2005 Forums

Transact-SQL (2005)

put a dash in zipcode

Author

Topic

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-03-04 : 14:41:18

Hello,

How would i write a query to update the current table to have the zip code format with dash between 9 digits (example: 19567-1020). Some are report only first 5 digit.

Thanks,

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-04 : 14:49:37

Will this do? But your zip code should be defined as char(10) or equivalent.

UPDATE table
SET zipcode = LEFT(LTRIM(RTRIM(zipcode)),5) + '-' + RIGHT(LTRIM(RTRIM(zipcode)),4)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-04 : 14:50:20

Sorry..the above wont work...I dint read ur line that says some may have only 5 digits..let me modify it.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-04 : 14:52:18

Maybe this?

update @t SET zipcode =
case LEN(LTRIM(RTRIM(zipcode)))
when 9
then
LEFT(LTRIM(RTRIM(zipcode)),5) + '-' + RIGHT(LTRIM(RTRIM(zipcode)),4)
else
zipcode
end

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-04 : 15:47:22

maybe this also?
update @t SET zipcode =
case LEN(LTRIM(RTRIM(zipcode)))
when 9
then
STUFF(zipcode, 5, 0, '-')
else
zipcode
end

subhash chandra
Starting Member

40 Posts

Posted - 2009-03-04 : 16:24:00

I think in yosiasz code replace STUFF(zipcode, 5, 0, '-') with STUFF(zipcode, 6, 0, '-').

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-04 : 16:31:01

oops yes sorry about that.

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-03-05 : 07:15:01

Thanks very much! It works very well.

quote:
Originally posted by vijayisonly

Maybe this?

update @t SET zipcode =
case LEN(LTRIM(RTRIM(zipcode)))
when 9
then
LEFT(LTRIM(RTRIM(zipcode)),5) + '-' + RIGHT(LTRIM(RTRIM(zipcode)),4)
else
zipcode
end

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-03-05 : 07:17:44

Yosiasz, your below code is working well too. I just changed the #5 to #6 like Chandra mentioned.

Thank you All for helping me!

quote:
Originally posted by yosiasz

maybe this also?
update @t SET zipcode =
case LEN(LTRIM(RTRIM(zipcode)))
when 9
then
STUFF(zipcode, 5, 0, '-')
else
zipcode
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-05 : 07:59:49

[code]DECLARE @Sample TABLE
(
ZipCode VARCHAR(20)
)

INSERT @Sample
SELECT '195671020' UNION ALL
SELECT '12345'

SELECT ZipCode,
COALESCE(STUFF(ZipCode, 6, 0, '-'), ZipCode) AS Peso
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources