如何在Oracle SQL查询中给数字添加前导零？

Question

我正在使用COUNT()函数在查询中检索名为removal_count的列。在结果集中，removal_count的数据类型是BIGDECIMAL。我想将数字转换为五位数。因此，如果值小于五位数，则应以前导零表示。

例如：1）如果删除计数为540，则显示00540
    2）如果删除计数为60，则显示00060

如果removal count是整数/字符串值，则可以使用Java表达式添加前导零：

--if removal_count is integer--
String.format("%05d",removal_count)

--if removal_count is string--
("00000"+removal_count).subString(removal_count.length())

我们是否可以将removal_count从big decimal转换为字符串或整数，以便我可以使用给定的Java表达式？否则，是否有任何方法在查询本身中添加前导零？

- Madhusudan

Answer 1

有两种方法可以实现。

方法1

使用LPAD函数。

例如，

SQL> WITH DATA(num) AS(
  2  SELECT 540 FROM dual UNION ALL
  3  SELECT 60 FROM dual UNION ALL
  4  SELECT 2 FROM dual
  5  )
  6  SELECT num, lpad(num, 5, '0') num_pad FROM DATA;

       NUM NUM_P
---------- -----
       540 00540
        60 00060
         2 00002

SQL>

WITH 子句仅用于构建演示样本数据，在实际查询中，请执行以下操作：

lpad(removal_count, 5, '0')

请记住，数字不能有前导零。上述查询的输出是一个字符串而不是一个数字。

方法2

使用TO_CHAR和格式模型：

SQL> WITH DATA(num) AS(
  2  SELECT 540 FROM dual UNION ALL
  3  SELECT 60 FROM dual UNION ALL
  4  SELECT 2 FROM dual
  5  )
  6  SELECT num, to_char(num, '00000') num_pad FROM DATA;

       NUM NUM_PA
---------- ------
       540  00540
        60  00060
         2  00002

SQL>

更新：为了避免用于负号的额外前导空格，请在 TO_CHAR 格式中使用 FM：

不使用 FM：

SELECT TO_CHAR(1, '00000') num_pad,
  LENGTH(TO_CHAR(1, '00000')) tot_len
FROM dual;

NUM_PAD    TOT_LEN
------- ----------
 00001           6 

使用 FM：

SELECT TO_CHAR(1, 'FM00000') num_pad,
  LENGTH(TO_CHAR(1, 'FM00000')) tot_len
FROM dual;

NUM_PAD    TOT_LEN
------- ----------
00001            5

- Lalit Kumar B

---

这种类型的问题在stackoverflow上已经有答案了。如何在Oracle中显示数字前导零。 - Sumit Jambhale

嗨@Lalit，小修改。方法1仅限于正数。方法2适用于负数，但对于正数会产生前导空格以表示缺少的符号。有没有办法除去它（除了使用substr）？ - Marmite Bomber

@MarmiteBomber，我验证了第二种方法，并发现它在带有正负号的正数和负数中都能正常工作。检查输出的长度，两者应该是相同的。 - Lalit Kumar B

@LalitKumarB 这里有一个例子：SELECT lpad(-1, 5, '0') m1, length(to_char(1, '00000')) m2 num_pad FROM dual;。方法一可以得到 000-1。方法一对于正数和负数都有效；我经常遇到的问题是正数前面的空格（用长度6演示）。希望能够找到一种解决方案，使得 无符号整数 总是具有长度5。 - Marmite Bomber

Answer 2

使用 LPAD pl/sql 函数，详见http://www.techonthenet.com/oracle/functions/lpad.php。

removal_count = 540 
LPAD(TO_CHAR(removal_count), 5, '0');
Result: '00540'

- adamliesko

Answer 3

在sqlplus中，您可以使用col格式命令：

SQL> select 540 aa, 540 bb from dual ;

    AA         BB

---

   540        540

SQL> col bb format 00000

SQL> /

（注：此处为保留原文，无需翻译）

    AA     BB

---

   540  00540

SQL>

- Merk