Home » SQL & PL/SQL » SQL & PL/SQL » Spacing.
Spacing. [message #234945] Thu, 03 May 2007 09:20 Go to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Hi all.

I have a select statement like this:

select rpad(ID_CODE,5,' ')||BR||NAME from prospect;

Output:
12436#Lolita
12439Kimberly
12430#Louise
12431#Wayne
12432#Erin
12433Sheila

Because the columm BR [varchar2(1)] has some null value rows and I want the output leave a space for BR if it is null.

For example, I would like the output leave a space between 12439 & Kimberly
and 12433 & Sheila.

Would you please to show me how to do? I would appreaciate that.
Re: Spacing. [message #234947 is a reply to message #234945] Thu, 03 May 2007 09:28 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
nvl(br, ' ')
Re: Spacing. [message #234948 is a reply to message #234945] Thu, 03 May 2007 09:30 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
NVL does the trick:
SQL> WITH yourtable AS
  2    (
  3      SELECT 12436 id_code, '#'  BR, 'Lolita'   thename FROM dual UNION ALL
  4      SELECT 12439 id_code, NULL BR, 'Kimberly' thename FROM dual UNION ALL
  5      SELECT 12430 id_code, '#'  BR, 'Louise'   thename FROM dual UNION ALL
  6      SELECT 12431 id_code, '#'  BR, 'Wayne'    thename FROM dual UNION ALL
  7      SELECT 12432 id_code, '#'  BR, 'Erin'     thename FROM dual UNION ALL
  8      SELECT 12433 id_code, NULL BR, 'Sheila'   thename FROM dual
  9    )
 10  SELECT id_code||NVL(br,' ')||thename yourstring
 11  FROM   yourtable
 12  /

YOURSTRING
-------------------------------------------------
12436#Lolita
12439 Kimberly
12430#Louise
12431#Wayne
12432#Erin
12433 Sheila

6 rows selected.


MHE
Re: Spacing. [message #234953 is a reply to message #234947] Thu, 03 May 2007 09:36 Go to previous message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thank you very much.
Previous Topic: Queries regarding Associative Arrays (merged topics)
Next Topic: ORA-01858: a non-numeric character was found where a numeric was expected
Goto Forum:
  


Current Time: Sun Dec 04 23:08:16 CST 2016

Total time taken to generate the page: 0.08462 seconds