CODEWITHSUNDEEP
mysqlsql
Peter Hon
Peter Hon

Reputation: 27

Mysql how to increase serial number

I have stored procedure in MySql which user will pass in a number e.g. 'cr002149' Now I want to increase the number by 1 for each record in select statment. How can I do that to let the number become e.g. cr002150,cr002151...

Thanks.

Upvotes: 0

Views: 136

Answers (1)

kba
kba

Reputation: 19466

You should reconsider your data structure, you might get better luck just using an auto_increment'd integer. Either way, this should do.

CONCAT('cr',
  LPAD(
    CAST(
      SUBSTRING('cr002149',3) AS DECIMAL(0)
    )+1,
    6,'0'
  )
);

The above will return cr002150. First, we get 002149 using SUBSTRING, then cast the string 002149 to an integer and get 2149. Now, we increment this to get 2150, followed by left-padding it by 0s to get 002150, and finally we concatenate cr on the left side.

Upvotes: 3

Related Questions