Warm tip: This article is reproduced from serverfault.com, please click

LPAD without trimming in mysql

发布于 2019-02-27 15:33:36

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

  • Request : SELECT LPAD(12345,4,"0");
  • Excepted result : 12345
  • Result : 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).

  • Is there a build-in function that does what i'm looking For ?
  • Or is there an alternative to the function MAX() that would work in that situation ?
  • Or should I stick with the IF(LENGTH) solution and it's drawbacks ?

Edit :

ZEROFILL doesn't fit my needs because I also need the id without 0s. 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.

Questioner
Pierre Lezan
Viewed
0
Boshentz 2021-01-20 21:19:27

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.