delete FROM table_name WHERE column_name REGEXP ‘[^0-9]‘;
You might be an unlucky soul me where you need to maintain a bunch of old and poorly written code. You’ll also probably get a bunch of weird errors if you’re running recent versions of PHP and mysql locally such as “invalid integer value xxx” when trying to modify a record. This occurs because the application is probably trying to insert a string into an INT field in mysql. Anyway, since the number of bugs are too numerous to fix in this application, the easiest solution is to remove the strict settings from your mysql.
To view the current sql mode setting:
To remove all settings (and allow string to get inserted into INT cols)
SET global sql_mode="";
What you do, don’t go to the control panel -> services and stop the service. I did this and was unable to log back into mysql, even after a reboot of my machine. The way to do it is via the command line and enter “net stop MySQL_ZendServer51″ and “net start MySQL_ZendServer51″ .
Using Mac OS X on the command line, log into mysql and select your database. Then run the following for a tab delimited file located on your desktop:
load data LOCAL infile ‘/Users/YOURNAME/Desktop/standards.txt’ into table TABLE_NAME fields terminated by “\t” lines terminated by “\r”
Download the remote file
scp my_username_on_remote_server@remote_server_ip_address:fullPathToFileOnRemoteServer pathToPutFileOnLocalMachine
If the file was zipped, then we’ll need to unzip it locally
gunzip -f pathToFileOnLocalMachine
Then load the data into the database
mysql -u root -p -h localhost databaseName < pathToUnzippedFileOnLocalMachine