field with extra spaces
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: field with extra spaces

  1. #1
    Join Date
    Mar 2004
    Posts
    53

    field with extra spaces

    There is a field with extra spaces at the end. I wanted to trim the spaces to right before displaying query output. rtrim won't work.

    I also tried substr(fieldname,1,length(fieldname)). It still displays some spaces. But, substr(fieldname,1,30) displays 30 chars only. Any ideas?

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Can you give us an example showing that RTRIM() doesn't remove charaters from the right????
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jan 2001
    Posts
    515

    Spaces??

    Are you sure they are spaces? Even thought they may look like spaces they might be something else.

  4. #4
    Join Date
    Mar 2004
    Posts
    53
    This is a varchar2(60) field which has extra spaces at the end.

    length(fieldname) = length(rtrim(fieldname))

    substr(fieldname,1,length(fieldname)) displays spaces (visible on toad and in an unix file)

    substr(fieldname,1,30) displays 30 chars only. (field ends at the last char in toad as well as unix)

    length(fieldname) = 30

    Let's say a field's name is Functionname and it's values are:

    OracleProgramming
    OracleplsqlProgramming


    substr(fieldname,1,length(fieldname)) shows something like this on toad:

    OracleProgramming |
    OracleplsqlProgramming |

    and substr(functionname,1,22) on toad shows:

    OracleProgramming |
    OracleplsqlProgramming|

    Here length(OracleplsqlProgramming) = 22

    It's the same on an unix file. I can see the spaces( or nulls ??) In unix file, the line splits ....The unix file is created from a shell script. When I substring the fieldname to its length (Ex: 30) it displays normally on unix file. If I don't substring or substring it to length(fieldname), those blanks/nulls/extra chars appear, therefore splitting the line into two seperate lines on unix file

    TRIMSPOOL is already ON

  5. #5
    Join Date
    Mar 2004
    Posts
    53
    Sorry

    substr(fieldname,1,length(fieldname)) shows something like this on toad:

    Code:
    OracleProgramming               |  
    OracleplsqlProgramming          |
    I can see spaces/nulls ..

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You should have mentioned originally that this is an issue with spooling SQL*Plus data to a file.

    If length(fieldname) = length(rtrim(fieldname)) then you have no spaces at the end ... however they are getting into your output file, they are not contained in the actual data, as stored in the database.

    How are you generating this file? Give us the exact script and SQL*Plus options.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Mar 2004
    Posts
    53
    sql script:


    sqlplus -s /nolog << ENDSQL
    connect user/password@inst;
    set pagesize 0;
    set feedback off;
    set echo off;
    set verify off;
    set linesize 100;
    spool txtfile;
    set trimspool on;
    select statement;
    spool off;
    ENDSQL

  8. #8
    Join Date
    Mar 2004
    Posts
    53
    Infact it is:


    sqlplus -s /nolog << ENDSQL
    connect user/password@inst;
    set pagesize 0;
    set feedback off;
    set echo off;
    set verify off;
    set linesize 100;
    set trimspool on; # before spooling
    spool txtfile;
    select statement;
    spool off;
    ENDSQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width