- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Stripping Blanks from SQL*Plus output
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-03-2004 09:48 PM
тАО11-03-2004 09:48 PM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-03-2004 10:14 PM
тАО11-03-2004 10:14 PM
Re: Stripping Blanks from SQL*Plus output
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-03-2004 11:14 PM
тАО11-03-2004 11:14 PM
Solutioncreate 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-03-2004 11:17 PM
тАО11-03-2004 11:17 PM
Re: Stripping Blanks from SQL*Plus output
I attached solution and output.
Regards
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-04-2004 01:42 AM
тАО11-04-2004 01:42 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-04-2004 01:47 AM
тАО11-04-2004 01:47 AM
Re: Stripping Blanks from SQL*Plus output
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-04-2004 02:12 AM
тАО11-04-2004 02:12 AM
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.