2

I have a column named unique_id which can take values,

16-01
16-10,
16-250,
16-1594

etc

16 is the year(eg.2016) and 01 is a count for something in this year. I manage to get this format using a BEFORE INSERT TRIGGER. Now I want to have 4 char format in count number like

16-0001
16-0010
16-0250
16-1594

How can I manage this formatting?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Patroklos Patroklou
  • 190
  • 2
  • 6

2 Answers2

2

You can use LPAD(yourcolumn, 4, '0');)

select
    concat(left(data,3), lpad(substring(data, 4), 4, '0'))
from test

DEMO HERE

And check manual here.

Blank
  • 12,308
  • 1
  • 14
  • 32
2

Use:

UPDATE Table1
SET Column1=concat(left(Column1,3), lpad(substring(Column1, 4), 4, '0'));

SQL Fiddle

Alternatively, RegEx may be used to replace using MYSQL UDF.

Ani Menon
  • 27,209
  • 16
  • 105
  • 126
  • I think there is no big difference between `UPDATE` and `SELECT`, and what I've provided is just a way to solve problem, if you agree with that, you can upvote it, there is no need post this again here. – Blank Jun 22 '16 at 07:56
  • I agree. No difference for me If it's select or update – Patroklos Patroklou Jun 22 '16 at 08:07
  • 1
    you can do this update using https://github.com/hholzgra/mysql-udf-regexp – afuc func Jun 22 '16 at 08:20
  • 1
    @PatroklosPatroklou sorry I felt it was the answer to the question as a SELECT merely prints the output rather than update the table. – Ani Menon Jun 22 '16 at 08:34