how to trim leading zeros from alphanumeric text in mysql function
MysqlMysql Problem Overview
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".
Mysql Solutions
Solution 1 - Mysql
You are looking for the trim() function.
Alright, here is your example
SELECT TRIM(LEADING '0' FROM myfield) FROM table
Solution 2 - Mysql
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:
Solution 3 - Mysql
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%';
Solution 4 - Mysql
I believe you'd be best off with this:
SELECT TRIM(LEADING '0' FROM myField)
Solution 5 - Mysql
simply perfect:
SELECT TRIM(LEADING '0' FROM myfield) FROM table
Solution 6 - Mysql
just remove space between TRIM ( LEADING
use
SELECT * FROM my_table WHERE TRIM(LEADING '0' FROM accountid ) = '00322994'
Solution 7 - Mysql
TRIM will allow you to remove the trailing, leading or all characters. Some examples on the use of the TRIM function in MySQL:
select trim(myfield) from (select ' test' myfield) t;
>> 'test'
select trim('0' from myfield) from (select '000000123000' myfield) t;
>> '123'
select trim(both '0' from myfield) from (select '000000123000' myfield) t;
>> '123'
select trim(leading '0' from myfield) from (select '000000123000' myfield) t;
>> '123000'
select trim(trailing '0' from myfield) from (select '000000123000' myfield) t;
>> '000000123'
If you want to remove only a select amount of leading/trailing characters, look into the LEFT/RIGHT functions, with combination of the LEN and INSTR functions
Solution 8 - Mysql
SELECT TRIM(LEADING '0' FROM *columnName*) FROM *tableName* ;
This also work correctly