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?

score 6 · Answer 1 · answered Apr 30 '12 at 17:15

6

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.

answered Apr 30 '12 at 17:15

eggyal

122,705
18
212
237

Can you use string operations on integer columns in mysql? I wasn't sure. – dqhendricks Apr 30 '12 at 21:26
@dqhendricks: See the link in my answer: they'll automatically get cast to strings (in the connection's character set as of 5.5.3, or as `binary` before that; either way, I believe `SUBSTRING` and `RLIKE '^999'` should work fine). – eggyal Apr 30 '12 at 21:30

dynamic · Accepted Answer · 2012-04-30T17:19:24.587

If you don't want to make a complex query or if you need futher operation you can do:

while($r = mysql_fetch_assoc($q)) {
   $newTemplateID = (string) $r['template_id'];
   if (substr($newTemplateID,0,3) === 999 ) {
     $newTemplateID = substr($newTemplateID,3);

     mysql_query("UPDATE tbl 
                    SET template_id = {$newTemplateID} 
                    WHERE page_id = {$r['page_id']} 
                    LIMIT 1");
   }
}

MySQL: remove number prefix from all records

2 Answers2