516.555.9294

i have this phone number in a char field in MySQL and I want to use a query to make it like this

5165559294

is there a simple query to do this for all records?

I would hate to sit here and edit every one of them.

thanks all

Well, while I dont recall quit how to do it in SQL and considering its rather late i figured i would at least submit something that would work to accomplish what you want to do. Notice that you will need to change the $row[] var depending on how your database is setup. And you will need to edit the Select lines and Update lines to reflect your database. This is just an example that it can be done easily with php, sorry for the messy code, wrote it on the fly in this reply heh..

Hope it works for you !


<?

//Simple update script to update multiple records.
//This script relies on the fact that you have an ID to determine
//seperate records. The database I used to test this on was setup as follows.
//
//CREATE TABLE test (
// id int(3) NOT NULL auto_increment,
// phone_num varchar(25) NOT NULL default '0',
// UNIQUE KEY id (id)
//) TYPE=MyISAM;
//Edit the $row[] vars as needed.


//Set this record to anything you need to update.
$record = "516.555.9294";
mysql_connect("localhost", "username", "password")
or die("Could not connect to server.");
mysql_select_db("database")
or die("Could not select database.");
$query = "SELECT * FROM test WHERE phone_num='" . $record . "'";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
$temp = explode(".",$row[1]);
$temp = $temp[0] . $temp[1] . $temp[2];
$query = "UPDATE test SET phone_num='" . $temp . "' WHERE id='" . $row[0] . "'";
mysql_query($query);
}
echo"Job Done!";

?>

thank you, that would work fine. the only thing that i noticed was that the users places all kinds of different chars. not just "."

maybe users, you can't live with them and you can't kill them. The application that they were using the user can put any format for the phone number. I'm big into error checking... I just some programmers are not

John

Hmm.


/* assume $db is a descriptor for an established database link */
$del = array('.','-','/',' ');
$s = "UPDATE mytable SET phone_num=REPLACE(phone_num,'%s','')";
foreach($del as $d)mysql_query(sprintf($s,$d),$db);

that's great tooooo. you are on a roll today..

Originally posted by jtrovato
that's great tooooo. you are on a roll today..

:spiny: