SQL Query to remove a dash in a field

dstjohnjr used Ask the Experts™
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:


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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008
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.


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