Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Spaces in varchar2() field

    We are using a tool called Cosmos to transfer data from Sybase to Oracle. It seems that in some cases it is transferring character data into varchar2 fields with trailing spaces. I thought that varchar2 fields were used specifically to omit trailing spaces when data is saved.

    so a code '03' is actually saved as '03 ' (apostrophes omitted in data)

    Could someone explain to me the behavior I should expect out of a varchar2 when leading spaces are possible?

    Thanks,
    Chuck

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    VARCHAR2 will save trailing spaces if they are present; the difference from CHAR is that CHAR will store trailing spaces even when they were not present!
    Code:
    SQL> create table t1 (vc varchar2(10), c char(10));
    
    Table created.
    
    SQL> insert into t1 values ('a','a');
    
    1 row created.
    
    SQL> insert into t1 values ('b  ','b  ');
    
    1 row created.
    
    SQL> select vc, length(vc), c, length(c)
      2  from t1;
    
    VC         LENGTH(VC) C           LENGTH(C)
    ---------- ---------- ---------- ----------
    a                   1 a                  10
    b                   3 b                  10

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    So if you need the trailing spaces removed, then use

    update my_table
    set my_column = rtrim(my_column);

    commit;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105
    better yet, trim the data on extraction...I'm sure cosmos is SQL-centric

    SELECT sybasetrimfunction(column) FROM etc....

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Right. Looks like the person who performed the import neglected to purposefully trim this field.

    And thanks for clarifying the reason why there may be trailing spaces in a varchar2 field.

    -Chuck

Posting Permissions

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