Substring replacement in MySQL

Question

I have fields like this table in mysql database,

<table border=1 ><tr><td>No</td><td>Value</td></tr>
<tr><td>1</td><td>System Code id(19032809128); unique list system</td></tr>
  <tr><td>2</td><td>System Code id(526371576351726); unique list system</td></tr>
  <tr><td>3</td><td>System Code id(162837120831092); unique list system</td></tr>
  <tr><td>4</td><td>System Code id(31-329-103912); unique list system</td></tr>
</table>

can someone tell me, how to remove only this string part "id(.....);" where values in id is not same every fields. remove with mysql query.

Answer 1

You do with php

   echo "<table border='1'>
  <tr>
  <th>No</th>
  <th>Value</th>
  </tr>";

  while($row = mysqli_fetch_array($result))
   {
   echo "<tr>";
   echo "<td>" . $row['No'] . "</td>";
   echo "<td>" . $row['Value'] . "</td>";
   echo "</tr>";
   }
   echo "</table>";

Answer 2

Very strange requirement; after you get rid of that information, every line will be identical except for the value under heading No. Here's a query that will do that (though I don't see why you'd want to):

select No, 'System Code  unique list system' as Value from table where 1;

Latest word from SO is that regex replace is not a function in mysql. So your alternative, if you want to do a true regex replace, is to use php/python/etc.

Answer 3

Should be somthings like this

select No, concat( 'System Code  (',Your_value_column , ') unique list system' ) as Value
 from your_table;

Answer 4

MySQL does not have a function to do regular expression replacement.

Here's an example of a query that does the specific example in your question. But if you have other cases (like multiple replacements to make in a single string), this will have to serve as a guideline.

SELECT no, CONCAT(
   SUBSTRING(value, 1, LOCATE('id(', value)-1), 
   SUBSTRING(value, LOCATE(');', value)+3)) AS value
FROM mytable;

Demo: SQLFiddle

It would be easier to do this in any programming language that supports regular expression string replacements. For example, fetch the string as is, and do the replacement in PHP.

---

Here's an example:

$pdo = new PDO(...);

$stmt = $pdo->query("SELECT no, value FROM mytable");

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $no = $row["no"];
  $value = preg_replace("/id\\([0-9-]+\\); /", "", $row["value"]);
  print "<tr><td>$no</td><td>$value</td></tr>\n";
}