Mysql number-string formatting

Question

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

How can I manage this formatting?

Similar question: Adding a leading zero to some values in column — Ani Menon
– Ani Menon, Commented Jun 22, 2016 at 8:49

Blank · Accepted Answer · 2016-06-22 07:05:29Z

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.

edited Jun 22, 2016 at 7:05

answered Jun 22, 2016 at 6:46

Blank

12.4k1 gold badge16 silver badges34 bronze badges

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

Comments

Ani Menon · Accepted Answer · 2016-06-22 08:29:58Z

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.

edited Jun 22, 2016 at 8:29

answered Jun 22, 2016 at 7:49

Ani Menon

28.4k17 gold badges111 silver badges128 bronze badges

4 Comments

Blank Over a year ago

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.

Patroklos Patroklou Over a year ago

I agree. No difference for me If it's select or update

afuc func Over a year ago

you can do this update using github.com/hholzgra/mysql-udf-regexp

Ani Menon Over a year ago

@PatroklosPatroklou sorry I felt it was the answer to the question as a SELECT merely prints the output rather than update the table.

Collectives™ on Stack Overflow

Mysql number-string formatting

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related