I don't want just 4. It's variable length and I want the actual number of valid bytes.
Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]
-----Original Message-----
Sent: Thursday, September 19, 2002 9:51 AM
To: '[EMAIL PROTECTED]'; Carle, William T (Bill), ALCAS
Subject: RE: sqlplus question
If you want only 4 bytes, us the SUBSTR function to take only what you need.
SQL> select ename, job
2 from emp;
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
SQL> set colsep '|'
SQL> /
SMITH |CLERK
ALLEN |SALESMAN
WARD |SALESMAN
JONES |MANAGER
SQL> select substr(ename,1,4), job
2 from emp;
SMIT|CLERK
ALLE|SALESMAN
WARD|SALESMAN
JONE|MANAGER
-----Original Message-----
Sent: Thursday, September 19, 2002 9:19 AM
To: Multiple recipients of list ORACLE-L
Howdy,
I am spooling my sqlplus output to a file with no headings and all the
fields separated by a delimiter. I have a field that is defined as
varchar2(56), but typically only 4 or 5 bytes are filled. Oracle recognizes
that and if you select length(fld1) from the table, you will get 4. But if I
spool this to a file, I always get the full 56 bytes padded with blanks. In
other words, I get 4 bytes of data and 52 blanks for that field. I only want
the four valid bytes so that my delimiter comes immediately after that 4th
byte. My sqlplus options are as follows:
set newpage 0 space 0 linesize 5000 pagesize 0 echo off recsep off feedback
off
heading off trimspool on colsep "|"
Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Carle, William T (Bill), ALCAS
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Carle, William T (Bill), ALCAS
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
