Remove zeros from SQL

How can I delete the digit 0 in MySQL only for codes that have 7 digits?

Example:

  • 0100396 (wipes 0 from the front)
  • 010611 (will not be deleted)

Answer

If you want to view your data this way, use the following select:

SELECT IF(code REGEXP '^0[0-9]{6}$', RIGHT(code, 6), code) AS code
FROM yourTable;

If you want to update your table, then use this:

UPDATE yourTable
SET code = RIGHT(code, 6)
WHERE code REGEXP '^0[0-9]{6}$';

Note that this assumes that your code column is text, and not actually a number. In the latter case, there are no leading zeroes.

Leave a Reply

Your email address will not be published. Required fields are marked *