Re: Lost last space

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Sat, 10 Nov 2007 14:31:43 -0000
Message-ID: <1194705103.328814.62910@k79g2000hse.googlegroups.com>

On Nov 10, 4:42 am, "astalavista" <nob..._at_nowhere.com> wrote:
> OK thanks ...
>
> "Mark D Powell" <Mark.Pow..._at_eds.com> a écrit dans le message de news:
> 1194566305.649460.163..._at_v23g2000prn.googlegroups.com...
>
> > On Nov 8, 5:09 pm, gazzag <gar..._at_jamms.org> wrote:
> >> On 8 Nov, 20:13, "astalavista" <nob..._at_nowhere.com> wrote:
>
> >> > Hi,
>
> >> > I have a table with the last column
> >> > with only one space
> >> > I try to generate a CSV
>
> >> > set colsep '$'
> >> > set linesize 500
> >> > => a lot of space at the end of record
> >> > set trimspool on
> >> > => no space at the end of a record
>
> >> > How can I get a CSV
> >> > with exactly one space at the end of a record ?
> >> > (as it is in the table)
>
> >> > Thanks for your help
>
> >> I've just re-read your post. CSV stands for Comma Separated Values.
> >> i.e. Each field is delimited by a comma (","). Do I understand you
> >> correctly?
>
> >> -g- Hide quoted text -
>
> >> - Show quoted text -
>
> > Since a trimspool will remove all trailing spaces including the one
> > you want how about using case or decode to write out a constant to
> > represent the space then feed the output into sed or another utility
> > or script to replace the single constant in the last position of a
> > line with a space.
>
> > Note if you will be using sqlldr to reload this file into another
> > Oracle db you can use the sqlldr control file to perform the
> > substitution of space for the constant.
>
> > HTH -- Mark D Powell --

If you are on 10.x, you could also use regular expression on the last column (if that is the only one about which you are concerned). You could then just not worry about trimspool, as you would be rolling your own trim. What is below may need some work, but it will get you started...

SQL> create table t1110(c varchar2(10))
  2 /

Table created.

SQL> insert into t1110 values('1')
  2 /

1 row created.

SQL> insert into t1110 values('2 ')
  2 /

1 row created.

SQL> insert into t1110 values('3 new ')   2 /

1 row created.

SQL> column len format 9
SQL> column orig format a10
SQL> column newone format a10
SQL> column new_len format 9
SQL> set trims off
SQL> select length(c) len,
  2         c orig,
  3         regexp_replace(c, '\W+$', ' ') newone,
  4         length(regexp_replace(c, '\W+$', ' ')) new_len
  5     from t1110

  6 /

LEN ORIG NEWONE NEW_LEN
--- ---------- ---------- -------

  1 1          1                1
  5 2          2                2
  7 3 new      3 new            6

SQL> HTH, Steve Received on Sat Nov 10 2007 - 08:31:43 CST