Home » RDBMS Server » Server Administration » Supressing blank spaces
Supressing blank spaces [message #373827] Mon, 14 May 2001 19:25 Go to next message
Tara Kibler
Messages: 2
Registered: May 2001
Junior Member
I want to output a few rows of data from a table to a file. Right now, I'm just spooling a file and doing 'select * from table' and my output file has a ton of blank spaces in it. How do I supress the blank spaces and only get the data in the output file, with each column delimited by something (space, comma, etc.)?

I have tried formatting the columns, changing the linesize, 'trimming' the rows. I don't know what else to try....

Thanks,
Tara
Re: Supressing blank spaces [message #373834 is a reply to message #373827] Mon, 14 May 2001 22:08 Go to previous messageGo to next message
Sarada
Messages: 27
Registered: April 2001
Junior Member
The best thing I would suggest is to use the concanetation.
For eg.SELECT TNAME, TABTYPE, CLUSTERID FROM TAB;
will return always with lot of white spaces immaterial of your formatting and all.
CUSTOMER TABLE
CUSTOMER_INFO TABLE
CUSTOMER_IOT TABLE
CUSTOMER_TAB TABLE
DATE_TST TABLE
DEPT TABLE
EMP TABLE
EMP_VIEW VIEW
EMP_VIEW2 VIEW
EXECUTABLES TABLE
Use as follows:

SET PAGES 0
SELECT TNAME||','||TABTYPE||','||CLUSTERID FROM TAB;

CUSTOMER,TABLE,
CUSTOMER_INFO,TABLE,
CUSTOMER_IOT,TABLE,
CUSTOMER_TAB,TABLE,
DATE_TST,TABLE,
DEPT,TABLE,
EMP,TABLE,
EMP_VIEW,VIEW,
EMP_VIEW2,VIEW,
EXECUTABLES,TABLE,

Ofcourse you need to do some conversions for non-character datatype. But it is worth it. Anyother settings you need can be added and spooled to a file. That file can be used as datafile for SQLLOADER.

HTH
Orashark
Re: Supressing blank spaces [message #373835 is a reply to message #373827] Mon, 14 May 2001 22:13 Go to previous messageGo to next message
Sarada
Messages: 27
Registered: April 2001
Junior Member
The best thing I would suggest is to use the concanetation.
For eg.SELECT TNAME, TABTYPE, CLUSTERID FROM TAB;
will return always with lot of white spaces immaterial of your formatting and all.
CUSTOMER TABLE
CUSTOMER_INFO TABLE
CUSTOMER_IOT TABLE
CUSTOMER_TAB TABLE
DATE_TST TABLE
DEPT TABLE
EMP TABLE
EMP_VIEW VIEW
EMP_VIEW2 VIEW
EXECUTABLES TABLE
Use as follows:

SET PAGES 0
SELECT TNAME||','||TABTYPE||','||CLUSTERID FROM TAB;

CUSTOMER,TABLE,
CUSTOMER_INFO,TABLE,
CUSTOMER_IOT,TABLE,
CUSTOMER_TAB,TABLE,
DATE_TST,TABLE,
DEPT,TABLE,
EMP,TABLE,
EMP_VIEW,VIEW,
EMP_VIEW2,VIEW,
EXECUTABLES,TABLE,

Ofcourse you need to do some conversions for non-character datatype. But it is worth it. Anyother settings you need can be added and spooled to a file. That file can be used as datafile for SQLLOADER.

HTH
Orashark
Re: Supressing blank spaces [message #373843 is a reply to message #373827] Tue, 15 May 2001 11:52 Go to previous message
Andrew again...
Messages: 270
Registered: July 2000
Senior Member
-- Set the column separator to whatever you want. ' ' is the default
set colsep ','
-- Trims trailing spaces off each line outputted.
set trimspool on

-- To show all settings
show all
-- to show a specific setting
show colsep
Previous Topic: alter column width
Next Topic: Index not use???
Goto Forum:
  


Current Time: Mon Dec 23 09:55:52 CST 2024