PHP mysql update without integers

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.

I am not sure if preg_replace will work like that in a query since it is a PHP function and not a MySQL/SQL function. — Maximus2012, Mar 24 '15 at 19:40
there's a couple of pure mysql options- but first what does the data look like — , Mar 24 '15 at 19:43
In that case you will need to calculate the value separately and write an update query that makes use of that calculated value. — Maximus2012, Mar 24 '15 at 19:43
it will work but not like that, you need to encapsulate it with " . preg_replace() . " — CodingInTheUK, Mar 24 '15 at 19:45
If you can install a library, there's: https://github.com/mysqludf/lib_mysqludf_preg More here: http://dba.stackexchange.com/questions/50210/regex-with-update-query-my-sql — bloodyKnuckles, Mar 24 '15 at 19:47
I tried $sql = "update `arms_ms` set colB= " .preg_replace("/[0-9]/", "", colA). ""; — Adreamcathcer Adreamcatcher, Mar 24 '15 at 19:48
Mysql does not have something inbuilt like `preg_replace` in php, either you need to write a stored procedure or write a `replace` chain to replace digits from 0 to 9 with `''` in a string. — Abhik Chakraborty, Mar 24 '15 at 19:50
http://stackoverflow.com/questions/11431831/remove-all-numeric-characters-from-column-mysql — Abhik Chakraborty, Mar 24 '15 at 19:51

score 0 · Answer 1 · answered Mar 24 '15 at 20:30

MySQL does not support regex replace.

Although it's a bit tedious, you can do it this way:

update arms_ms set
colB = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(colA,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')

PHP mysql update without integers

1 Answers1