Operating System - HP-UX
1748194 Members
4073 Online
108759 Solutions
New Discussion юеВ

Re: Stripping Blanks from SQL*Plus output

 
SOLVED
Go to solution
Michael Campbell
Trusted Contributor

Stripping Blanks from SQL*Plus output

Hi

Does anyone know how to avoid getting blanks at the end of records when spooling to a file using SQL*plus. i.e. if a record ('|' delimited) is spooled as

CL |CLS|CLS4127 |2001123112 |ABCD |6895000000|CAN |GO000| |IEP| 0| | | | |PHXJOB | | | | | | | | | | | | | | |

is there a way to get it to spool as

CL|CLS|CLS4127|2001123112|ABCD|6895000000|CAN|GO000||IEP|0|||||PHXJOB|||||||||||||||

We are using 8.1.7.4

Any Help Appreciated

Michael
6 REPLIES 6
Yogeeraj_1
Honored Contributor

Re: Stripping Blanks from SQL*Plus output

hi,

try using "replace".

e.g.
YD@MYDB.MU> select
replace(
replace('CL |CLS|CLS4127 |2001123112 |ABCD |6895000000|CAN |GO000| |IEP| 0| | | | |PHXJOB | | | | | | | | | | | | | | |',' |','|'),'| ','|')
from dual;


REPLACE(REPLACE('CL|CLS|CLS4127|2001123112|ABCD|6895000000|CAN|GO000||IEP|0|||||
PHXJ
________________________________________________________________________________
____
CL|CLS|CLS4127|2001123112|ABCD|6895000000|CAN|GO000||IEP|0|||||PHXJOB|||||||||||
||||



hope this helps!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Jean-Luc Oudart
Honored Contributor
Solution

Re: Stripping Blanks from SQL*Plus output

write your own function :

create or replace function trim10blank(
InString CHAR
) return CHAR
IS
Begin
if InString = ' ' then
return ' ';
else
return InString;
end if;
end;
####################################
SQLWKS> select 'AA' || trim10blank(' ') || 'ZZ' from dual
2>
'AA'||TRIM10BLANK('')||'ZZ'
--------------------------------------------------------------------------------
AA ZZ
1 row selected.
SQLWKS> select 'AA' || trim10blank(' zz') || 'ZZ' from dual
2>
'AA'||TRIM10BLANK('ZZ')||'ZZ'
--------------------------------------------------------------------------------
AA zzZZ
1 row selected.

Regards
Jean-Luc
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: Stripping Blanks from SQL*Plus output

As the Web site has some effect in spaces,
I attached solution and output.

Regards
Jean-Luc
fiat lux
Hein van den Heuvel
Honored Contributor

Re: Stripping Blanks from SQL*Plus output


Or you could post process the spooled file with a simple:

perl -pe 's/ +\|/|/g' input > output

This will remove any trailing spaces from a field, but will not remove spaces in the middle of a field.

-p = loop through input and print $_ to output
-e = program follows on line

s///g = repeated substitutions in the line
" +\|" = one or more spaces followed by a |


hth,
Hein.

Michael Campbell
Trusted Contributor

Re: Stripping Blanks from SQL*Plus output

Hein

Thanks, but the reason I want to strip out the blanks is because the spool file is too big. About 33,000,000 rows would dump to a spool file of around 70Gb.

Regards

Michael
Hein van den Heuvel
Honored Contributor

Re: Stripping Blanks from SQL*Plus output


Ok, understood. So now it will only be 30GB huh ? :-) :-)


btw... you might still consider a postprocess solution with perl / awk / ed / tr or whatever your preference is, because contrary to a sqlplus solution it can deal with all fields in one whack.
You would just have to make sure not to have the spool file land on the disk, but send it to a pipe.

1) Either with a mknod and have a slave process read teh pipe, transform and output

2) or the classic pipe:

echo "@report" | sqlplus -s user/pass | perl 'transform' | gzip > trimmed-and-compressed.rpt.gz

Cheers,
Hein.