I am trying to find a way to add 0 at the beginning of an ID without trimming it :
I am currently using LPAD(id,4,"0")
witch works fine until 9999
SELECT LPAD(12345,4,"0");
12345
1234
I am Looking for a Request who does LPAD(id, MAX(LENGTH(id),4), "0")
I found SELECT IF(LENGTH(12345)>4, 12345, LPAD(12345, 4, "0"));
but i would prefer if the 4
was in a single place (to make it easier use it elsewhere).
MAX()
that would work in that situation ?IF(LENGTH)
solution and it's drawbacks ?Edit :
ZEROFILL
doesn't fit my needs because I also need the id without 0
s.
I mainly use the ID without the LPAD()
, but when I do, I use it with a prefix : CONCAT("PFX", LPAD(id,4,"0"))
Thanks for your Help
PS: please tell me if i do anything wrong, it's my first time asking here.
Well I had similar problem with LPAD, it was truncating number to its pad length. According to https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_lpad it is expected result.
As far as I can see, nobody mentioned answer that solved my same problem:
LPAD(id, GREATEST(LENGTH(id), 4), "0")
It works as expected. Pads any id
's shorter than 4 characters with 0, and returns unchanged id
's that are longer than 4 characters.
I'm leaving my answer here for other people, that will find this question in the future.
That's what i've ended up doing, as advised at the end of the accepted answer
I don't see any answer with solution provided by me. Everyone else is using some mix of
SUBSTRING
's,CONCAT
's orREGEXP_REPLACE
's. I posted this answer, so anyone else that finds this question (as I did) will be provided few possibilities :)My bad, I misread Daniel's answer, your answer was what i was looking for back then