CODEWITHSUNDEEP
mysql
Patroklos Patroklou
Patroklos Patroklou

Reputation: 190

Mysql number-string formatting

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?

Upvotes: 2

Views: 112

Answers (2)

Ani Menon
Ani Menon

Reputation: 28199

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.

Upvotes: 2

Blank
Blank

Reputation: 12378

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.

Upvotes: 2

Related Questions