MySQL - Repeat a varchar according to the leading zeros

user_11444326 0
Viewed 64

Using MYSQL, I have a varchar column and I need to execute a select, repeating/padding the return value according to the leading zeros, for example:

ID Return
000001 XXXXX000001
011111 X011111

Basically if the string have leading zeros I need to padding the return value with the same X value
Is it even possible? I've been searching about, I've tried LPAD, TRIMM, but I can't figure it out

Thanks!!!

mysql
2 Answers

You can get the number of leading 0s of the ID with:

LENGTH(ID) - LENGTH(ID + 0)

or:

LENGTH(ID) - LENGTH(TRIM(LEADING '0' FROM ID))

Use this number with the function REPEAT() to create a string with repeating Xs and then concatenate it to ID:

SELECT ID, 
       CONCAT(REPEAT('X', LENGTH(ID) - LENGTH(ID + 0)), ID) `Return`
FROM tablename

Or:

SELECT ID, 
       CONCAT(REPEAT('X', LENGTH(ID) - LENGTH(TRIM(LEADING '0' FROM ID))), ID) `Return`
FROM tablename

See the demo.

user_104988280

If your values are always Numeric with leading zero then try this

select id, lpad(id,2*length(id)-length(id+0),'X') as `return` from test

DEMO

user_77724830
Related