MySQL查询中如何去除字段开头的所有零？

问题场景 #

数据库中有一张ApprovalTable表，ApprovalNumber字段存储带前导零的字符串型数字，示例数据如下：

SNO     ApprovalNumber
----    -----------
1       0007890677
2       0090780788
3       767078
4       009033
5       05097
6       005765078

需求是执行SELECT查询时，去除该字段开头的所有零，得到预期结果：

SNO     ApprovalNewNo
----    -----------
1       7890677
2       90780788
3       767078
4       9033
5       5097
6       5765078

你尝试的语句未生效：

SELECT LTRIM(ApprovalNumber,"0") AS ApprovalNewNo
FROM `ApprovalTable`  

解决方案 #

问题出在LTRIM函数的用法上，不同数据库的字符串处理函数语法有差异，以下是主流数据库的正确写法：

1. MySQL/MariaDB

MySQL的LTRIM()仅支持移除空格，要移除指定前导字符需用TRIM(LEADING)语法：

SELECT 
  SNO,
  TRIM(LEADING '0' FROM ApprovalNumber) AS ApprovalNewNo
FROM `ApprovalTable`;

2. SQL Server

SQL Server的LTRIM()支持指定要移除的字符，但参数顺序为LTRIM(要移除的字符, 字符串)，且需用单引号包裹字符：

SELECT 
  SNO,
  LTRIM('0', ApprovalNumber) AS ApprovalNewNo
FROM ApprovalTable;

3. Oracle

Oracle可直接用LTRIM()指定要移除的前导字符：

SELECT 
  SNO,
  LTRIM(ApprovalNumber, '0') AS ApprovalNewNo
FROM ApprovalTable;

处理全零特殊情况

如果字段值全为零（如0000），上述语句会返回空字符串，可通过CASE语句处理为0：

-- 以MySQL为例
SELECT 
  SNO,
  CASE 
    WHEN TRIM(LEADING '0' FROM ApprovalNumber) = '' THEN '0'
    ELSE TRIM(LEADING '0' FROM ApprovalNumber)
  END AS ApprovalNewNo
FROM `ApprovalTable`;

原语句无效原因 #

• MySQL中LTRIM()仅接受一个参数，无法指定移除的字符；
• 多数数据库要求字符串常量用单引号而非双引号，原语句中的"0"会被识别为标识符而非字符串；
• SQL Server中LTRIM()的参数顺序错误，正确顺序是(要移除的字符, 目标字符串)。

内容的提问来源于stack exchange，提问作者Niranjan Kumar Chowdam