PHP MYSQL preg_replace or REGEXP

Question

Need a PHP MYSQL query that will copy data from one column into another while stripping it of numbers before placing into the new column. Tried the following:

$sql = "SELECT time FROM `arms`";
$results = $conn->query($sql);
$sql = "update `arms` set colB=preg_replace("/[0-9]/", "", 'colA')";
$results = $conn->query($sql);

Also tried

$sql = "update arms_ms set colB= " .preg_replace("/[0-9]/", "", colA). "";

Also tried

$sql = "update arms_ms set colB= colA REGEXP /^([^0-9]*)$/;

Have even tried

$sql = "update arms_ms set colB= common_schema.replace_all(colA,'0123456789','') ";

None of the above work.

PHP and MySQL are *entirely separate things*, and you can't just slip PHP functions into a MySQL query like that. Additionally, MySQL doesn't provide a string editing function in the way that you need, [`REGEXP` doesn't work like that] so you'll need to SELECT all of the data, modify it in PHP, and then issue UPDATE statements to modify the data in MySQL. — Sammitch, Mar 24 '15 at 20:25

score 0 · Answer 1 · edited May 23 '17 at 10:10

0

MySql can chain replace function, like described here: Can MySQL replace multiple characters?

In your case, with 10 numbers, it's not very comfortable, but leads to your goal.

A more elaborated way could be a defined function like described here: Removing numbers from string in mysql

I'm sorry for using the answer function for somethin like a comment, but I'm not able to give comments.

edited May 23 '17 at 10:10

Community

1
1

answered Mar 24 '15 at 20:31

Guardian667

417
4
16

score 0 · Answer 2 · answered Mar 24 '15 at 20:35

As others have pointed out, you can't simply edit SQL queries in place or arbitrarily inject PHP into a query and expect it to work. You need to fetch the column value you want, strip the numbers, and then perform a second query to update the desired column/row:

$sql = "SELECT colA FROM arms WHERE blahblah";
$row = $conn->query($sql)->fetch_array(MYSQLI_ASSOC);
$col = preg_replace(/[0-9]/", "", $row['colA']);
$sql = "UPDATE arms SET colB = $col WHERE blahblah";
$conn->query($sql);

score 0 · Accepted Answer · answered Mar 24 '15 at 20:35

Something like:

// get records
$sql1 = "SELECT primary_key, colA, colB FROM `arms`";
$results = $conn->query($sql1);
// prepare statement
$sql2 = "UPDATE `arms` SET colB=? WHERE primary_key=?";
$stmt = $conn->prepare($sql2);
// loop & update
foreach( $results as $record ) {
  $args = array(
    preg_replace("/[0-9]/", "", $record['colA']),
    $record['primary_key']
  );
  $stmt->execute($args);
}

But this is written more with PDO in mind and may need some tweaking if you're using MySQLi.

PHP MYSQL preg_replace or REGEXP

3 Answers3