Mysql Update Using Regex for Materialized Path Structured Data

Question

How can i perform an update query in mysql where i match the beginning of a string and replace that matched portion with another string?

The background of my problem is that I'm using materialized paths to structure my hierarchical data and need this type of query to move a node with all of it's children to another node.

Thanks for the help!

Answer 1

Try something like:

SELECT REPLACE(column, "search","replace") FROM table WHERE column LIKE "search%";

Cheers!