OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Spacing.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Spacing. [message #234945]

Thu, 03 May 2007 09:20

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.

Report message to a moderator

Re: Spacing. [message #234947 is a reply to message #234945]

Thu, 03 May 2007 09:28

Cthulhu
Messages: 381
Registered: September 2006
Location: UK

Senior Member

nvl(br, ' ')

Report message to a moderator

Re: Spacing. [message #234948 is a reply to message #234945]

Thu, 03 May 2007 09:30

Maaher
Messages: 7065
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

Report message to a moderator

Re: Spacing. [message #234953 is a reply to message #234947]

Thu, 03 May 2007 09:36

lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia

Senior Member

Thank you very much.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 22:07:31 CST 2024