MySQL append , prefix a range of numbers with 0.

Question

I have around a few thousand rows with which contain 3 digit numbers starting with 100 and ranging to 199 which i need to prefix with 0. There are also thousands of other numbers 4 digit numbers as well which i don't want to change.

I need find all the 3 digit numbers in the range and prefix only those ranging from 100 -199 with a 0 so as they are 4 digits eg 100 > 0100 , 104 > 0104 and so on. Also these numbers may step eg 110 next is 124.

Is there a way I can do this using SQL? as i don't fancy changing these manually!

Many Thanks

Answer 1

This is best done with a programming language. That said, here's a SQL query that will update all the existing numbers:

UPDATE tableName SET fieldName = right(concat('0000',fieldName), 4) WHERE length(fieldName) < 4

Answer 2

The LPAD function is what you are looking for. You can use this in your query to pad the numbers on the fly.

SELECT LPAD(CONVERT(num AS CHAR), 4, '0') FROM tbl WHERE num > 99 AND num < 200

If you prefer to do this on the script side, str_pad will do the same in php.