Adding a single Digit to a 4-digits long number in mysql-CodePudding

I'm trying to get a mysql script, that changes every 4-digit long number "into" a 5-digit long, by adding a "0" at the start of each number. This is, what I tried:

SELECT * FROM `customer_address_entity_text` WHERE CHAR_LENGTH(value) < 5;
SELECT CONCAT("0", CAST(value as CHAR(50)) AS value;

but it shows an error, that there is no field "value" found:

#1054 - Unknown field 'value' in field list (translated)

would be nice, if someone could help me with this.

(it also gives out this error, when I'm not trying to Cast 'value' to a CHAR)

tl;dr: I want 'value = "0" value' in mysql

example: 'value = 1234; value = "0" value; value = 01234' and that in mysql

CodePudding user response：

Two issues:

There is a missing closing parenthesis for CONCAT(
Your second SELECT has no FROM clause, so indeed there is no value field there.

So move that CONCAT expression inside the first SELECT clause and balance the parentheses:

SELECT c.*, CONCAT("0", CAST(value as CHAR(50))) AS value 
FROM `customer_address_entity_text`  c
WHERE CHAR_LENGTH(value) < 5;

If your purpose is to pad all values with zeroes so they get 5 digits, so that it also transforms 1 to "00001" and 12 to "00012", then use LPAD:

SELECT c.*, LPAD(value, 5, "0") AS value 
FROM `customer_address_entity_text`  c;

To update the value field:

UPDATE `customer_address_entity_text` 
SET value = LPAD(value, 5, "0");

Or, with your original concat version:

UPDATE `customer_address_entity_text` 
SET value = CONCAT("0", CAST(value as CHAR(50)))
WHERE CHAR_LENGTH(value) < 5;