Fill values with zeros between letters and numbers [duplicate]

Question

We had our inventory in an Excel list until a few months ago. This was maintained manually. The inventory numbers look something like this:

PC1
PC80
PC560
PC1080

I moved the directory to an inventory system called Snipe-IT which is based on MariaDB. The search does not work correctly due to the different length inventory numbers. If you search for example "PC" and sort it descending by the Asset-Tag there should be the highest number at the top. But there is PC999 at the top. Therefore I have to adjust the database so that each inventory number looks like this:

PC000001
PC000080
PC000560
PC001080

Unfortunately, I am not that experienced in SQL. How do I manage to fill the zeros between the prefix and the numbers appropriately?

Everything is installed on a Ubuntu 21.04 machine with Apache2.

Search results

Answer 1

You could use LPAD to create equal length identifiers, but REGEXP_SUBSTR is only available in MySQL 8.

UPDATE table_name
SET column = CONCAT(REGEXP_SUBSTR(column, "[A-Z]+"), LPAD(REGEXP_SUBSTR(column,"[0-9]+"), 6, '0'))

I haven't tested this, I would take a copy of the table you want to update and make sure it works before running it for real.

What it's trying to do is, update the column with the inventory number to be the identifier

REGEXP_SUBSTR(column, "[A-Z]+")

and join that to the zero padded number (padded to always be 6 digits in length)

LPAD(REGEXP_SUBSTR(column, "[0-9]+"), 6, '0')

If the prefix part (PC) is always the same then that makes it easier, I am just assuming you will have different identifiers, but if not:

UPDATE table_name
SET column = CONCAT('PC', LPAD(REGEXP_SUBSTR(column, "[0-9]+"), 6, '0'))

This should work in MySQL 5.6 assuming the string part at the beginning is always two characters:

UPDATE table_name
SET column = CONCAT(LEFT(column , 2), LPAD(REPLACE(column, LEFT(column, 2), ''), 6, '0'))

SELECT query example here http://sqlfiddle.com/#!9/8f3503/6