如何在多个oracle中显示前导零 [英] How to display the leading zero's in a number of oracle

问题描述

我有一个oracle列(artnr)，其长度为1，类型为number(9).我要更新以下号码...

I have an oracle column(artnr) contains a length of 1 which is of type number(9). I want to update the number as following...

示例:

如果数字为0，则应为00000 如果数字为1，则应为00001 如果数字为12，则应为00012

If number is 0 then it should be 00000 If number is 1 then it should be 00001 If number is 12 the it should be 00012

记住:这里的00000,0000和00012是数字数据类型

Remember : here 00000,0000, and 00012 are of number datatypes

以下是我尝试过但失败的方法.

The following are the methods I have tried but failed..

UPDATE pitb.toestel b
   SET b.artnr = LPAD (b.artnr, 5, 0)
 WHERE b.idinventaris = 403743;

失败，因为Lpad只能应用于字符串

Failed because Lpad can only be applied on strings

UPDATE pitb.toestel b
   SET b.artnr = TO_NUMBER (TO_CHAR (artnr, '00009'), '00009')
 WHERE b.idinventaris = 403743;

仍然失败，因为to_number将不会显示前导零.只会从第一个数字开始考虑

Still failed, because to_number will not display the leading zero's. It will only consider from first number

任何人，请您给我建议一些可以解决此问题的方法.

Anyone, could you please suggest me something which will solve this scenario..

sql比pl/sql解决方案更可取

sql is preferrable than pl/sql solution

推荐答案

如果数字为0，则应为00000；如果数字为1，则应为00000 为00001，如果数字为12，则应为00012

If number is 0 then it should be 00000 If number is 1 then it should be 00001 If number is 12 the it should be 00012

记住:这里的00000,0000和00012是数字数据类型

Remember : here 00000,0000, and 00012 are of number datatypes

首先，数字没有以零开头的数字.因此，当您存储NUMBER值时，就让它们表现得像NUMBER.仅当要显示它们时，才可以使用LPAD并添加前导零.将数字转换为带前导零的字符串.

Firstly, Numbers don't have leading zero's. So, when you store the NUMBER values, you let them behave like NUMBERs. it is only when you want to display them, you can use LPAD and add the leading zeroes. Which conevrts the number to a string with leading zeroes.

因此，无需更新表.使用LPAD以所需的方式显示它们.

So, no need to update the table. Use LPAD to display them the way you want.

 SQL> WITH DATA AS
  2    ( SELECT 1 ID FROM DUAL UNION ALL
  3      SELECT 11 ID FROM DUAL
  4    )
  5  SELECT
  6     LPAD(ID,5, 0) id
  7  FROM DATA
  8  /

ID
-----
00001
00011

为避免隐式数据类型转换，请在应用LPAD之前使用TO_CHAR.

To avoid, implicit data type conversion, use TO_CHAR before applying LPAD.

这篇关于如何在多个oracle中显示前导零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！