how to trim leading zeros from alphanumeric text in mysql function

Question

What mysql functions are there (if any) to trim leading zeros from an alphanumeric text field?

Field with value "00345ABC" would need to return "345ABC".

Answer 1

You are looking for the trim() function.

Alright, here is your example

SELECT TRIM(LEADING '0' FROM myfield) FROM table

Answer 2

TIP:

If your values are purely numerical, you can also use simple casting, e.g.

SELECT * FROM my_table WHERE accountid = '00322994' * 1

will actually convert into

SELECT * FROM my_table WHERE accountid = 322994

which is sufficient solution in many cases and also I believe is performance more effective. (warning - value type changes from STRING to INT/FLOAT).

In some situations, using some casting function might be also a way to go:

http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

Answer 3

If you want to update one entire column of a table, you can use

USE database_name;
UPDATE `table_name` SET `field` = TRIM(LEADING '0' FROM `field`) WHERE `field` LIKE '0%';