Mysql how to increase serial number

Question

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.

will cr remain always the same

iJade
– iJade

2013-08-06 03:53:34 +00:00
Commented Aug 6, 2013 at 3:53 — iJade
– iJade, Commented Aug 6, 2013 at 3:53
have you tried anything?

Praveen Prasannan
– Praveen Prasannan

2013-08-06 03:54:25 +00:00
Commented Aug 6, 2013 at 3:54 — Praveen Prasannan
– Praveen Prasannan, Commented Aug 6, 2013 at 3:54

kba · Accepted Answer · 2013-08-06 04:05:38Z

3

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.

edited Aug 6, 2013 at 4:05

answered Aug 6, 2013 at 3:59

kba

19.5k6 gold badges65 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql how to increase serial number

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related