Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195

    Unanswered: Wierd Column Data Padding

    I have a column in a table that is of VARCHAR2 datatype allowing 30 characters. When I insert a row into the table, the column in question seems to add spaces to the end of the value in the column so that it is 30 characters long. Can someone tell me how to stop this? Thanks, Jeremy

    P.S. - I need to insert:

    'Testing'

    But:

    'Testing '

    is getting inserted.
    Nothing better than a good ride.

  2. #2
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I knew that would happen. Here is the problem:

    I need to insert:

    Code:
    'Testing'
    but

    Code:
    'Testing                       '
    is ending up as the value in the column. Thanks, Jeremy
    Nothing better than a good ride.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sounds more like a CHAR than a VARCHAR2 - how are you doing the insert exactly?

  4. #4
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    You are correct. How do I get around this? Thanks, Jeremy

    Code:
    IM TABLE DESCRIPTION
    
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     IM_PLANNER                                         NUMBER(3)
     IM_EX_STATUS                                       VARCHAR2(1)
     IM_COMMODITY                                       CHAR(3)
     IM_KEY                                             VARCHAR2(30)
     IM_DESCR                                           CHAR(30)
     IM_CRDATE                                          DATE
     IM_MDDATE                                          DATE
     IM_E_DATE                                          DATE
     IM_C_DATE                                          DATE
     IM_BUR_PCT                                         NUMBER(5,2)
     IM_BUR_PER_UNIT                                    NUMBER(9,4)
     IM_SAVE_DEMAND_SW                                  NUMBER(1)
     IM_DIM_CNT                                         NUMBER(1)
     IM_DIM_CONV                                        NUMBER(11,6)
     IM_RACN                                            NUMBER(5)
     IM_LLC                                             NUMBER(3)
     IM_ON_HAND                                         NUMBER(15,6)
     IM_SAFETY                                          NUMBER(15,6)
     IM_LEADTIME                                        NUMBER(5)
     IM_TYPE                                            NUMBER(1)
     IM_POLICY                                          NUMBER(1)
     IM_CLASS                                           NUMBER(1)
     IM_QTY                                             NUMBER(15,6)
     IM_DAYS                                            NUMBER(3)
     IM_MIN                                             NUMBER(15,6)
     IM_MAX                                             NUMBER(15,6)
     IM_MULT                                            NUMBER(15,6)
     IM_C_ISSUE                                         NUMBER(11,6)
     IM_C_RECV                                          NUMBER(11,6)
     IM_ISSUE_SW                                        NUMBER(1)
     IM_STOCK_SW                                        NUMBER(1)
     IM_PLAN_SW                                         NUMBER(1)
     IM_PHANTOM_SW                                      NUMBER(1)
     IM_HOLD                                            NUMBER(13,6)
     IM_COUNTS                                          NUMBER(3)
     IM_ALLOC                                           NUMBER(15,6)
     IM_C_CNT                                           NUMBER(5)
     IM_SENS                                            NUMBER(3)
     IM_FENCE                                           NUMBER(3)
     IM_USAGE                                           NUMBER(15,6)
     IM_REC_YTD                                         NUMBER(15,6)
     IM_SCHED                                           NUMBER(3)
     IM_SN_REQ                                          NUMBER(1)
     IM_STD_MAT                                         NUMBER(13,4)
     IM_STD_LAB                                         NUMBER(13,4)
     IM_STD_BUR                                         NUMBER(13,4)
     IM_LOW_MAT                                         NUMBER(13,4)
     IM_LOW_LAB                                         NUMBER(13,4)
     IM_LOW_BUR                                         NUMBER(13,4)
     IM_BUD_MAT                                         NUMBER(13,4)
     IM_BUD_LAB                                         NUMBER(13,4)
     IM_BUD_BUR                                         NUMBER(13,4)
     IM_ACT_MAT                                         NUMBER(13,4)
     IM_ACT_LAB                                         NUMBER(13,4)
     IM_ACT_BUR                                         NUMBER(13,4)
     IM_AVG_MAT                                         NUMBER(13,4)
     IM_AVG_LAB                                         NUMBER(13,4)
     IM_AVG_BUR                                         NUMBER(13,4)
     IM_STD_LOT                                         NUMBER(13,6)
     IM_J_QTY1                                          NUMBER(13,6)
     IM_J_QTY2                                          NUMBER(13,6)
     IM_J_QTY3                                          NUMBER(13,6)
     IM_J_QTY4                                          NUMBER(13,6)
     IM_J_QTY5                                          NUMBER(13,6)
     IM_PRICES1                                         NUMBER(13,4)
     IM_PRICES2                                         NUMBER(13,4)
     IM_PRICES3                                         NUMBER(13,4)
     IM_PRICES4                                         NUMBER(13,4)
     IM_PRICES5                                         NUMBER(13,4)
     IM_LY_REC_YTD                                      NUMBER(15,6)
     IM_LY_USAGE                                        NUMBER(15,6)
     IM_COST_CONV                                       NUMBER(11,6)
     IM_COST                                            NUMBER(13,4)
     IM_SNLAB_REQ                                       NUMBER(1)
     IM_SNREC_REQ                                       NUMBER(1)
     IM_SNSHP_REQ                                       NUMBER(1)
     IM_SNISS_REQ                                       NUMBER(1)
     IM_SNLAB_ADD                                       NUMBER(1)
     IM_SNREC_ADD                                       NUMBER(1)
     IM_SNISS_ADD                                       NUMBER(1)
     IM_SNSHP_ADD                                       NUMBER(1)
     IM_SNLAB_WU                                        NUMBER(1)
     IM_CMLAB_REQ                                       NUMBER(1)
     IM_CMREC_REQ                                       NUMBER(1)
     IM_CMISS_REQ                                       NUMBER(1)
     IM_CMSHP_REQ                                       NUMBER(1)
     IM_CMLAB_ADD                                       NUMBER(1)
     IM_CMREC_ADD                                       NUMBER(1)
     IM_CMISS_ADD                                       NUMBER(1)
     IM_CMSHP_ADD                                       NUMBER(1)
     IM_SNLAB_COMPL                                     NUMBER(1)
     IM_SNLAB_DEVONLY                                   NUMBER(1)
     IM_SNISS_CONSREQ                                   NUMBER(1)
     IM_SNREC_ASKCONS                                   NUMBER(1)
     IM_SNISS_EXP                                       NUMBER(1)
     IM_HOME_VAT                                        NUMBER(2)
     IM_SSD_W_KG                                        NUMBER(15,6)
     IM_SSD_S_UNIT                                      NUMBER(15,6)
     IM_SCALE                                           NUMBER(1)
     IM_ACTIVITY_2                                      VARCHAR2(30)
     IM_BUYER                                           VARCHAR2(12)
     IM_AUTH                                            VARCHAR2(3)
     IM_DRAWING                                         VARCHAR2(12)
     IM_REV                                             VARCHAR2(4)
     IM_CATALOG                                         VARCHAR2(12)
     IM_ABC                                             VARCHAR2(1)
     IM_UNIT_S                                          VARCHAR2(2)
     IM_UNIT_I                                          VARCHAR2(2)
     IM_UNIT_R                                          VARCHAR2(2)
     IM_PROD_CODE                                       VARCHAR2(3)
     IM_GL_ACCT                                         VARCHAR2(12)
     IM_COST_UM                                         VARCHAR2(1)
     IM_TAX_CODE                                        VARCHAR2(2)
     IM_DIM_UNIT_1                                      VARCHAR2(2)
     IM_DIM_UNIT_2                                      VARCHAR2(2)
     IM_DIM_UNIT_3                                      VARCHAR2(2)
     IM_ENTITY_ID                                       VARCHAR2(12)
     IM_GL_PROD_CODE                                    VARCHAR2(12)
     IM_COS_ACCT                                        VARCHAR2(12)
     IM_SSD_COMMODITY                                   VARCHAR2(12)
     IM_SUPPLY_TYPE                                     VARCHAR2(2)
     IM_TOOLING                                         NUMBER(5,2)
     IM_ARTWORK                                         NUMBER(5,2)
     IM_PROGRAM                                         NUMBER(5,2)
    Nothing better than a good ride.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    SQL> ALTER TABLE IM MODIFY IM_DESCR VARCHAR2(30);

    Table altered.

    SQL> UPDATE IM SET IM_DESCR = RTRIM(IM_DESCR);

    xxx rows updated.

  6. #6
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    That will be my last resort. Is there anyway to get around this besides altering the table? I am trying to keep the EXACT same table structure as a DB that my ERP Software uses. Reason being is that I'm writing Quoting Software that once we win a bid, I will have to export the data from my Quoting DB to the ERP DB. Thanks, Jeremy
    Nothing better than a good ride.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by JCScoobyRS
    That will be my last resort. Is there anyway to get around this besides altering the table? I am trying to keep the EXACT same table structure as a DB that my ERP Software uses. Reason being is that I'm writing Quoting Software that once we win a bid, I will have to export the data from my Quoting DB to the ERP DB. Thanks, Jeremy
    In that case, their data will always be blank-padded, so presumably yours should be also. You can select RTRIM(im_descr) if you want it without trailing blanks - you could even create a view that does that for you.

    On the other hand, if you modify your table to use VARCHAR2 then when you export your data to their DB it will get blank-padded again anyway, so they'll never know the difference:

    Code:
    SQL> create table t1 ( im_descr varchar2(30) );
    
    Table created.
    
    SQL> create table t2 ( im_descr char(30) );
    
    Table created.
    
    SQL> insert into t1 values ( 'a' );
    
    1 row created.
    
    SQL> insert into t1 values ( 'abc' );
    
    1 row created.
    
    SQL> select im_descr||'.' from t1;
    
    IM_DESCR||'.'
    -------------------------------
    a.
    abc.
    
    SQL> insert into t2 select * from t1;
    
    2 rows created.
    
    SQL> select im_descr||'.' from t2;
    
    IM_DESCR||'.'
    -------------------------------
    a                             .
    abc                           .

  8. #8
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I took your first advise. I think that taking my VARCHAR2 datatype and inserting it into the CHAR column shouldn't be a problem. Thanks for your help, Jeremy
    Nothing better than a good ride.

Posting Permissions

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