MySQL trigger concat with curdate()

Question

i need a help to format this trigger output.

I need the output of the trigger have this format:

2013/0001
2013/0002
...

When year changes,

2014/0001
2014/0002
...

but my trigger generates that format:

00012013/

The trigger code is below:

SET NEW.num = CONCAT(
LPAD(COALESCE(
  (SELECT MAX(LEFT(num, 4)) 
     FROM `tb_numeros`
    WHERE num LIKE DATE_FORMAT(CURDATE(), '%Y/____')), 0) + 1, 4, '0'), 
DATE_FORMAT(CURDATE(), '%Y/'))

Álvaro González · Accepted Answer · 2013-11-27 17:45:17Z

1

You can use indentation to clarify what's going on:

SET NEW.num = CONCAT(
    LPAD(
        COALESCE(
            (
                SELECT MAX(LEFT(num, 4)) 
                FROM `tb_numeros`
                WHERE num LIKE DATE_FORMAT(CURDATE(), '%Y/____')
            ), 0
        ) + 1, 4, '0'
    ), 
    DATE_FORMAT(CURDATE(), '%Y/')
)

It's clear that you get year on second place because, well, it's the second clause inside CONCAT().

answered Nov 27, 2013 at 17:45

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

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

2 Comments

Marcelo Aymone Over a year ago

Im trying change order, but generates mysql error. Any sugestion about his order?

Álvaro González Over a year ago

How could I suggest anything? I don't know how you changed the code or what error you get now.

wchiquito · Accepted Answer · 2013-11-27 17:01:10Z

1

I do not understand how you get the number sequence, however, I think you can simplify the code a bit:

...
SET `get_year` := YEAR(NOW());
SET `sequence` := (SELECT... WHERE... `num`... LIKE... `get_year`...); /* RETURN 0001 */
SET NEW.`num` := CONCAT(`get_year`, '/', `sequence`);
...

answered Nov 27, 2013 at 17:01

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Comments

Marcelo Aymone · Accepted Answer · 2013-11-27 18:45:17Z

0

yeah! woRKS!!!

SET NEW.num = CONCAT(
DATE_FORMAT(CURDATE(), '%Y/'),
LPAD(
    COALESCE(
        (
            SELECT MAX(RIGHT(num, 4)) 
            FROM `tb_numeros`
            WHERE num LIKE DATE_FORMAT(CURDATE(), '%Y/____')
        ), 0
    ) + 1, 4, '0'
)
)

answered Nov 27, 2013 at 18:45

Marcelo Aymone

2913 silver badges19 bronze badges

Collectives™ on Stack Overflow

MySQL trigger concat with curdate()

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related