Results 1 to 11 of 11
  1. #1
    Join Date
    May 2003
    Posts
    58

    Unanswered: field with extra spaces

    There is a field that has extra spaces at the end. It is a varchar2 column. I wanted to trim the spaces to right before displaying query output. Any ideas? Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    use RTRIM function
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2003
    Posts
    58
    Originally posted by anacedent
    use RTRIM function
    Hi I used rtrim(fieldname). It still doesn't trim spaces.

  4. #4
    Join Date
    Feb 2004
    Posts
    108
    Hmm .. it's a varchar column. It should not show any blank spaces on right side.
    Check for the length
    SELECT col_name, LENGTH(col_name) L1, LENGTH(RTRIM(col_name)) L2
    FROM YourTable

    If l1 and l2 both are equal, then there is some problem displaying your data. May be your report is appending it - some item level property.

  5. #5
    Join Date
    May 2003
    Posts
    58
    length(fieldname) = length(rtrim(fieldname))

    Also,

    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

  6. #6
    Join Date
    May 2003
    Posts
    58
    Could it be nulls ?

  7. #7
    Join Date
    May 2003
    Posts
    58
    Any ideas please?

  8. #8
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by chatguy2020
    length(fieldname) = length(rtrim(fieldname))

    Also,

    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
    try do replace(field_name,' ','x') see what happen. Could be special characters look like space.

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    could he be referring to TRIMSPOOL in sqlplus?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Feb 2004
    Posts
    108
    Is this for few records or all ?
    If possible try coping that item value ( may be from TOAD) and paste it in notepad or word. As lynden.zhang mentioned, any special chars should show up there.

  11. #11
    Join Date
    May 2003
    Posts
    58
    Originally posted by pinakdb
    Is this for few records or all ?
    If possible try coping that item value ( may be from TOAD) and paste it in notepad or word. As lynden.zhang mentioned, any special chars should show up there.

    This is for all records. In toad, only text can be selected. After the text is selected, whatever whitespace is left at the end, appears as blanks and therefore splitting lines in unix. Hex-viewer displays them as ascii 20s.

Posting Permissions

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