Concatenation alternatives [message #35960] |
Thu, 25 October 2001 13:45 |
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 |
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 |
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 |
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 |
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.
----------------------------------------------------------------------
|
|
|