MySQL 如何从mysql函数中的字母数字文本中修剪前导零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/96952/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to trim leading zeros from alphanumeric text in mysql function
提问by Sean
What mysql functions are there (if any) to trim leading zeros from an alphanumeric text field?
有哪些 mysql 函数（如果有）可以从字母数字文本字段中修剪前导零？
Field with value "00345ABC" would need to return "345ABC".
值为“00345ABC”的字段需要返回“345ABC”。
回答by Chris Bartow
You are looking for the trim() function.
您正在寻找trim() 函数。
Alright, here is your example
好的，这是你的例子
SELECT TRIM(LEADING '0' FROM myfield) FROM table
回答by lubosdz
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).
在许多情况下这是足够的解决方案，而且我相信性能更有效。（警告 - 值类型从 STRING 更改为 INT/FLOAT）。
In some situations, using some casting function might be also a way to go:
在某些情况下，使用一些强制转换函数可能也是一种方法：
回答by theBuzzyCoder
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%';
回答by JustinD
I believe you'd be best off with this:
我相信你最好这样做：
SELECT TRIM(LEADING '0' FROM myField)
回答by Anna
TRIM will allow you to remove the trailing, leading or all characters. Some examples on the use of the TRIM function in MySQL:
TRIM 将允许您删除尾随、前导或所有字符。在 MySQL 中使用 TRIM 函数的一些示例：
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
如果您只想删除选定数量的前导/尾随字符，请查看 LEFT/RIGHT 函数，以及 LEN 和 INSTR 函数的组合
回答by Anna
simply perfect:
简直完美：
SELECT TRIM(LEADING '0' FROM myfield) FROM table
回答by Hemali
just remove space between TRIM ( LEADINGuse
只需删除TRIM ( LEADING使用之间的空间
SELECT * FROM my_table WHERE TRIM(LEADING '0' FROM accountid ) = '00322994'
回答by sanduniYW
SELECT TRIM(LEADING '0' FROM *columnName*) FROM *tableName* ;
This also work correctly
这也可以正常工作
