如何在Oracle SQL查询中的数字前添加零? [英] How to add leading zero in a number in Oracle SQL query?

问题描述

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

I am retrieving a column named removal_count in my query using COUNT() function. In result set the datatype of removal_count is BIGDECIMAL. I want to convert number into five digits. SO if value is less than five digits then it should be represented with leading zero's.

例如1)如果清除计数是540，则显示00540
2)如果清除计数为60，则显示00060

e.g 1) If removal count is 540 then display 00540
2) If removal count is 60 then display 00060

如果移除计数是整数/字符串值，那么我可以使用Java表达式添加前导零:

If the removal count is integer/string value then I can add leading zero's using java expression :

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

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

我们可以将removing_count转换为字符串或整数(从大十进制开始)，以便可以使用给定的java表达式吗?否则是否有任何方法可以在查询本身中添加前导零?

Can we convert removal_count into string or integer ( from big decimal) so that I can use given java expression? Or else is there any way to add leading zero's in query itself?

推荐答案

您可以通过两种方式实现.

You could do it in two ways.

方法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子句仅用于构建示例数据进行演示，在您的实际查询中只需执行以下操作:

The WITH clause is only to build sample data for demo, in your actual query just do:

lpad(removal_count, 5, '0')

请记住，数字不能具有前导零.以上查询的输出是字符串，而不是数字.

Remember, a number cannot have leading zeroes. The output of above query is a string and not a number.

方法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 :

Update : To avoid the extra leading space which is used for minus sign, use FM in the TO_CHAR format:

没有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

这篇关于如何在Oracle SQL查询中的数字前添加零?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！