From: adam beecher (adam at domain iewebs.com)
Date: Thu 04 May 2000 - 13:00:04 IST
> Anyone know how to trim any character that's not a letter, number,
> fullstop, or space from a certain column, in a certain MySQL table ? Turns
> out that silly people have been storing their "fullname" in the database
> of my email system, with colons, commas etc. You have no idea how much
> that upsets Qmail and sendmail. I've stuck some javascript in to stop
> people entering it...but that's not much good for the 5000 users that
> already could have done it...
>
Any restriction on language? How 'bout a PHP shell script? (You'll need to
check the regex - I'm not brilliant widdem. If you have a later version of
PHP, you can use the Perl Compatible regexes instead if you want):
#!/path/to/php
<?
mysql_connect("host","user","pass");
mysql_select_db("db");
$ri = mysql_query("SELECT uid,field FROM table");
while($rr = mysql_fetch_row($ri)) {
$rr[1] = eregi_replace("[^a-z0-9. ]*$","",$rr[1]);
mysql_query("UPDATE table SET field='$rr[1]' WHERE uid='$rr[0]'");
}
?>
You could be more selective with the SELECT statement - use RLIKE with an
appropriate regex to just get the records you want.
adam
This archive was generated by hypermail 2.1.6 : Thu 06 Feb 2003 - 13:06:01 GMT