Home » SQL & PL/SQL » SQL & PL/SQL » Concatenation alternatives
Concatenation alternatives [message #35960] Thu, 25 October 2001 13:45 Go to next message
andy
Messages: 92
Registered: December 1999
Member
I have a 20 fields from a table that need to be concatenated together to write to a flat file but since some of the fields contain null values they are not included in the concatenation.

I get:
JOHNDOETEST

Instead of:
JOHNDOE(20 blank spaces)TEST

Could someone point out another way to string these 20 fields together so that I can write them to a flat file.

----------------------------------------------------------------------
Re: Concatenation alternatives [message #35961 is a reply to message #35960] Thu, 25 October 2001 15:15 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
If you want each column padded out to a certain length regardless of whether it is null or not:

select rpad(nvl(c1, ' '), 20) || rpad(nvl(c2, ' '), 10)
from t

If you only want null values padded:

select nvl(c1, rpad(' ', 20)) || nvl(c2, rpad(' ', 10))
from t

----------------------------------------------------------------------
Re: Concatenation alternatives [message #35968 is a reply to message #35960] Fri, 26 October 2001 05:48 Go to previous messageGo to next message
andy
Messages: 92
Registered: December 1999
Member
Padding the cells with RPAD doesn't matter because concatenation in PL/SQL does not recognize the padding. I had already tried what you suggested to no avail.

----------------------------------------------------------------------
Re: Concatenation alternatives [message #35970 is a reply to message #35961] Fri, 26 October 2001 09:19 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You'll need to post exactly what you are doing because PL/SQL concatenation absolutely recognizes padding.

----------------------------------------------------------------------
Re: Concatenation alternatives [message #35973 is a reply to message #35961] Fri, 26 October 2001 14:44 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Andy, did you even look closely at the example I gave you? It clearly shows how to pad a null value using a combination of RPAD and NVL.

----------------------------------------------------------------------
Previous Topic: URGENT-Difference between PL/SQL and SQL*PLUS
Next Topic: Executing OS commands from PL?
Goto Forum:
  


Current Time: Thu Mar 28 14:56:44 CDT 2024