Replace syntax

Anon

Hello I have a mysql table which I need to delete a "-" from data that is in the Case field and replace it with nothing.

ie. current format 123-12 what would like to be 12312.

I have looked at the mysql and just can't seem to get the syntax correct to accomplish this.

I know this is a very trival question but would appreciate any help that I am given.

Thanks-
KCK

Anon

From the MySQL docs:

REPLACE(str,from_str,to_str)

Returns the string str with all all occurrences of the string from_str replaced by the string to_str:

mysql> select REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'

So in your case, try something like:

UPDATE TABLE tablename SET fieldname = REPLACE(fieldname, '-', '');

Test this on a dummy table first with records that you do not mind losing. I don't have time to test it out.

For further string manipulations in MySQL, RTM:

http://www.mysql.com/doc/S/t/String_functions.html

btubalinal

just use the explode function.
ex:

<?php
$Case = "123-12";

$arr = explode("-", $Case);

for($i=0; i<sizeof($arr); $i++) {
echo $arr[$i] . "<br>";
}

btubalinal

Sorry. I thought you wanted help with the PHP. I didn't realize you wanted help with the MySQL.

Anon

🙂

And that should actually be:

UPDATE tablename SET fieldname = REPLACE(fieldname, '-', '');

ronnieutd

Or do it like this...

$mysqlhostname = "localhost";
$mysqlusername = "user";
$mysqlpassword = "password";
$mysqldatabase = "mydatabase";
$tablename = "mytable";

mysql_connect($mysqlhostname,$mysqlusername,$mysqlpassword) or die("Unable to connect");
mysql_select_db("$mysqldatabase") or die("Unable to select database $mysqldatabase");

$query = "SELECT * FROM $tablename";
$result = mysql_query($query);

while ($row=mysql_fetch_array($result))
{
extract($row);
$newCase = str_replace("-","",$Case);
$query = "update INTO $mytable set case='$newCase' where case = '$Case'";

}

Done!

ronnieutd

Didn't test, small bug:

$query = "update INTO $mytable set case='$newCase' where case = '$Case'";

should be:

$query = "update $tablename set case='$newCase' where case = '$Case'";

Anon

Thanks everyone I got it with the help of everyone who responded.

Bart....I never even thought to do it in php.....I will have to keep that in mind...

Thanks again to all...
Khris

Anon

I'd like to echo Khris

Thanks a lot.