CODEWITHSUNDEEP
mysqlfieldupdates
Homer_J
Homer_J

Reputation: 3323

MySQL add data to an existing field

I have a field enq_id - it currently contains numbers such as:

80081
414365
567

Now, I need to update the field in two ways, firstly I need to find out if any of the numbers are more than 6 characters long (there shouldn't be but I need to check). I then need to set a character limit of 6 characters on that field and then, finally, add 0's to the beginning of any enq_id that isn't 6 characters long to make it 6 characters.

Any ideas?

Upvotes: 1

Views: 155

Answers (1)

inhan
inhan

Reputation: 7470

If the following returns the result you're trying to achieve:

SELECT
    `enq_id`,
    IF(CHAR_LENGTH(`enq_id`) < 6,
        LPAD(`enq_id`,6,'0'),
        SUBSTRING(`enq_id`,1,6)
    ) AS 'result'
FROM `some_table`
WHERE CHAR_LENGTH(`enq_id`) != 6

Then using the following will update your table accordingly.

UPDATE `some_table`
SET `enq_id` = 
    IF(CHAR_LENGTH(`enq_id`) < 6,
        LPAD(`enq_id`,6,'0'),
        SUBSTRING(`enq_id`,1,6))
WHERE CHAR_LENGTH(`enq_id`) != 6

Note that the SUBSTRING() function deletes all the characters after the 6th character, and LPAD adds preceding zeros (in the above example) if needed, to each record.

UPDATE: For some reason I added an extra condition. The optimized code(s) should have been:

SELECT `enq_id`,LPAD(`enq_id`,6,'0') AS 'result'
FROM `some_table`
WHERE CHAR_LENGTH(`enq_id`) < 6

and

UPDATE `some_table`
SET `enq_id` = LPAD(`enq_id`,6,'0')
WHERE CHAR_LENGTH(`enq_id`) < 6

Upvotes: 2

Related Questions