MySQL: remove number prefix from all records

Question

I have this page table below and I want to remove the numbering prefix of '999' from the column of template_id,

page_id    template_id    url
1          9991           a
2          9992           b
3          9993           c
4          4              d

so I can get the updated data below,

page_id    template_id    url
1          1              a
2          2              b
3          3              c
4          4              d 

Any idea how I can remove this kind of prefix?

Answer 1

To obtain the data as shown in your question:

SELECT
  page_id,
  SUBSTRING(template_id, IF(template_id RLIKE '^999', 4, 1)) AS template_id,
  url
FROM page

Or, if you want to permanently update the table:

UPDATE page
SET template_id = SUBSTRING(template_id, 4)
WHERE template_id RLIKE '^999'

MySQL's implicit type conversion will handle the rest.