SQL replace 文字列を置換してupdate

特にurl周りを変えたい場合とか便利。

例えばこんな感じ

# select * from images;
 id | entry_id | type |    image_url
----+----------+------+-----------------
  1 |        1 |    0 | http://kapi.jp/images/img_1_0
  2 |        2 |    1 | http://kapi.jp/images/img_2_1
  7 |       18 |    1 | http://kapi.jp/images/img_18_1
  4 |        4 |    1 | http://kapi.jp/images/img_4_1
  8 |       18 |    0 | http://kapi.jp/images/img_18_0
  6 |        4 |    0 | http://kapi.jp/images/img_4_0
  9 |        1 |    1 | http://kapi.jp/images/img_1_1
 12 |       19 |    1 | http://kapi.jp/images/img_19_1
 13 |       19 |    0 | http://kapi.jp/images/img_19_0
 26 |       23 |    1 | http://kapi.jp/images/img_23_1


# update
    images
set
    image_url=replace(
        image_url,
        'http://kapi.jp/images/',
        'images/');

       
# select * from images;
 id | entry_id | type |    image_url
----+----------+------+-----------------
  1 |        1 |    0 | images/img_1_0
  2 |        2 |    1 | images/img_2_1
  7 |       18 |    1 | images/img_18_1
  4 |        4 |    1 | images/img_4_1
  8 |       18 |    0 | images/img_18_0
  6 |        4 |    0 | images/img_4_0
  9 |        1 |    1 | images/img_1_1
 12 |       19 |    1 | images/img_19_1
 13 |       19 |    0 | images/img_19_0
 26 |       23 |    1 | images/img_23_1