MySQL Help REGEXP Trying to shorten Zipcodes from 9 to 5 digits

I have a MySQL table with addresses and some have 5 digit zip codes and some have 9 digit zip codes. A sample record would be like this

940 Huguenot Ave Staten Is开发者_StackOverflowland, NY 10312-4313

I need to standardize the database and make all records 5 digit zip codes. My sudo code for something like this was like this

If regex [0](5 numeric digits )-[1](4 numeric digits) then update table replace('-[1]','');

Any help would be appreciated! Thanks,

UPDATE --- zipcode is not an independent field, it is part of the address column, (a sample is above)

Success! I ended up using a combination of REGEX and SUBSTR using the length of the field -5. Thanks, guys!

MySQL regexes can only find/match text, they cannot modify the data in the tables. Of course, nothing says you can't search for zipcodes by regex, but then do the processing in a script.

SELECT ...
FROM ...
WHERE (zipcode REGEX '[[:digit:]]{5}-[[:digit:]]{4}')

Im not sure why you want to use regexp, but I would just do:

update table set zip = substring(zip,1,5);

I tried this and it worked beautifully

MySQL doesn't support regex replace operations. However, you could first find those records that contain a 9-digit ZIP code:

SELECT * FROM mytable WHERE ZIPcode REGEXP "[[:digit:]]{5}-[[:digit:]]{4}";

and then delete the five characters following the dash.

I've never used regex in MySQL, but it seems like a simpler regular expression would just check for the '-' character and delete everything after it in the string instead of checking for the right number of digits. Sorry I can't help you with code but hopefully it puts you on the right track.