SQL Query to remove a dash in a field

dstjohnjr
dstjohnjr used Ask the Experts™
on
I need to build a SQL query to remove a dash character in a field called Zip for all zip code values that DO NOT have data (numbers) after the dash.  So, for example, a lot of my Zip fields have data like:

80123-
80111-
80232-

I want to remove the dash in these particular scenarios.  TIA!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008
Commented:
update t
set zipcode = left(zipcode, 5)
where right(ltrim(rtrim(zipcode)),1) = '-'
Do you only want to remove a trailing dash?  That is, if the Zip is 23232-3232, should the dash remain or be removed?

For trailing only, use:

Update MyTable
SET Zip = LEFT(Zip, Len(Zip)-1)
WHERE Zip LIKE '_%-'

For all, use:

Update MyTable
SET Zip = REPLACE(Zip, '-'. '')
WHERE Zip LIKE '%-%'

These assume a SQL Server DB. Similar functions exist in Access, but this code is not guaranteed to work there.

Author

Commented:
Worked great!  You rock!  Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial