0

I have a database that I am trying to clean out. They have 70k entries with some bad values in the zip code column. I want to go through and delete the rows that do not have a valid 5 or 9 digit zip code. This the current statement I have:

SELECT * FROM `sample_requests` WHERE `request_zip` RegExp '^\d{5}$)|(^\d{5}-\d{4}$)'

I am not very familiar with regular expressions, but I know enough. Also this particular regex is used on the form now, so that it will not submit without a valid zip code so I know it works. I just can't get the MySQL portion to work.

JEFF B
  • 126
  • 2
  • 12

1 Answers1

1

you need to use DELETE instead of SELECT. Before you do this, you might want to create a duplicate 'sample_requests' table in case you make a mistake.

here's the command you're interested in (deleting the duplicate table rows first so you know it works):

DELETE FROM sample_requests_duplicate 
WHERE request_zip NOT RegExp '(^\d{5}$)|(^\d{5}-\d{4}$)'
tim peterson
  • 23,653
  • 59
  • 177
  • 299
  • Right. I have just been trying to get the RegExp portion to work and figured SELECT was a little safer than DELETE. :) Will the code that you gave me delete the good ones though? How do you say "WHERE !=" with regular expressions? – JEFF B May 08 '12 at 01:24
  • check my new answer, it has the `NOT` syntax (`=` or `!=` won't work), if that doesn't work, isn't there a `!` in Regex? I'm not the best at that so i don't know – tim peterson May 08 '12 at 01:31
  • Thank you. You answered my question. The NOT doesn't seem to work, so I will have to try and figure out how the Regex ! operator works. – JEFF B May 08 '12 at 01:44
  • @JEFFB great, glad to help, see the answer at the very bottom of this SO page for more on `!`/`NOT` operator in Regex: http://stackoverflow.com/questions/7317043/regex-not-operator – tim peterson May 08 '12 at 01:51