SQL replace() function in PDO

Question

I'd like to convert the instructions from this answer into a general script that can be reused across projects.

I'm wondering how these commands can be written in Drupal 7 PDO format:

UPDATE `system` SET filename = replace(filename, 'sites/all/modules/contrib', 'profiles/distroname/modules/contrib');

Is the mysql replace function something generic to all SQL, or is it mysql specific? I understand this is an expression, but I'm not clear on how to use the PDO expression syntax in this case.

What are the advantages of writing it PDO, as opposed to just a simple db_query() with the sql query from above? In PDO, will this truly be db agnostic?

Answer 1

Would it be something like this?

db_update('system')->
  expression('filename', 'replace(filename, :old, :new)', array(
    ':old' => 'sites/all/modules/contrib',
    ':new' => 'profiles/distroname/modules/contrib'
  ))
  ->execute();