Re: Problem with using NVL Function in SQL

From: Tony Andrews <andrewst_at_onetel.com>
Date: 26 Jan 2005 04:27:52 -0800
Message-ID: <1106742472.551654.158770@f14g2000cwb.googlegroups.com>

Seems to work OK for me. What happens when you do it?

SQL> create table t as select to_char(comm) as konten_nr from emp;

Table created.

SQL> select
LPAD(NVL(TO_CHAR(TO_NUMBER(konten_nr),'FM999999999'),'0'),8,'0'), konten_nr
2 from t;

LPAD(NVL KONTEN_NR
-------- ---------

00000000
00000300 300
00000500 500
00000000
00001400 1400
00000000
00000000
00000000
00000000
00000000 0
00000000
00000000
00000000
00000000
00000000

Not that I'd choose to do it that way:

SQL> select LPAD(NVL(konten_nr,'0'),8,'0'), konten_nr 2 from t;

LPAD(NVL KONTEN_NR
-------- ---------

00000000
00000300 300
00000500 500
00000000
00001400 1400
00000000
00000000
00000000
00000000
00000000 0
00000000
00000000
00000000
00000000
00000000 Received on Wed Jan 26 2005 - 06:27:52 CST