mysql去除字符串左边多余的0

/ mysql / 没有评论 / 173浏览


MySQL实现删除数据左右空格trim() 左空格ltrim() 右空格rtrim(),可以先把字符串所有0替代成空格,在去除掉左边的空格,再把空格替代成0。

SELECT
	a.id_number, REPLACE(ltrim(REPLACE(a.id_number, '0', ' ')), ' ', '0')
FROM
	healthy_service_bodypass_data a 
	LIMIT 100

结果如下:

00000000001234567890 1234567890

00000000020160520048 20160520048

00000000020160520047 20160520047

00000000020160520040 20160520040

00000000020160520049 20160520049

00000000020160520050 20160520050

00000000020160520051 20160520051

00000000020160520035 20160520035

00000000020160520045 20160520045

00000000020160520054 20160520054

00000000020160520053 20160520053

00000000020160520055 20160520055

点击这里给我发消息
嘿!有什么能帮到您的吗?