deleting specific data from one column in a table

[nkomokov]

I'm trying to delete all zip code data that doesn't conform to the USA standard 5 digit zip in column ZipCode.

Currently the column contains zips and postal codes in a wide variety of formats...

97217
97217-1811
V0N 1B0
ER34B7R88
97217, OR

Ideally, I would like to delete all the zip codes that contain letters and trim down the 9 digit codes to 5 digit codes, but I'd be happy to just delete the zip codes that contain letters.

Any suggestions?

 

[gmmastros]

Before you update your data to blank out records, you'll need to make absolutely sure that it is 'doing the right thing'.

Try running this query...

[green]--This query should return zipcode that meets your requirements[/green]
Select Left(ZipCode,5)
From <TableName>
Where Left(ZipCode,5) Like '[0-9]%'

[green]-- This query should return 'bad' zip codes[/green]
Select Left(ZipCode,5)
From <TableName>
Where Left(ZipCode,5) [red]Not[/red] Like '[0-9]%'
[/code]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

DELETE FROM MyTable
       WHERE NOT (Zip LIKE '[0-9][0-9][0-9][0-9][0-9]?[0-9][0-9][0-9]')

then

UPDATE MyTable SET Zip = LEFT(Zip,5)

something like that, but not tested

Borislav Borissov

 

[nkomokov]

Thanks George, I got it to work.

NOW I just need to know how to turn zips from

97217-1811

into

97217

How do I delete the last 5 characters from an entry?

 

[gmmastros]

Update Table
Set ZipCode = Left(ZipCode, 5)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[r937]

why would you want to destroy data?

as far as i know (but i'm in canada), the us postal service went to a standard 9 digit zip many years ago

my advice: leave the 9-digit zips alone, and if for some reason you want to show only the first 5, use LEFT() in the SELECT

as for deleting the non-numeric ones, that also is problematic

i wouldn't just blindly eradicate a valid canadian postal code, either -- unless you're removing that row completely



r937.com | rudy.ca

 

[nkomokov]

That was EASY! Thanks again George.

For the other poster...

We need a column that only contains 5 digit zip codes to do a radius search by zip. All other postal codes are copied in a separate column for mailing. (including canada)

 

[gmmastros]

Glad to help.

Just like r937, I was wondering, so I'm glad you explained.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom