Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    14

    Unanswered: How to change length

    Hi,

    I would like to shorten dataType from VARCHAR(80) to VARCHAR(6).

    Do you know how to write the SQL. I've checked all the data to make sure there is no data that is longer than 6 char.


    Thank you in advance.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example:

    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE test_alter_lenghth
    ( id  INTEGER NOT NULL PRIMARY KEY
    , str VARCHAR(80)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO test_alter_lenghth
    VALUES
      ( 1 , 'abcd' )
    , ( 2 , 'efghij' )
    , ( 3 , NULL );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test_alter_lenghth;
    ------------------------------------------------------------------------------
    
    ID          STR                                                                             
    ----------- --------------------------------------------------------------------------------
              1 abcd                                                                            
              2 efghij                                                                          
              3 -                                                                               
    
      3 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    ALTER TABLE test_alter_lenghth
    ALTER str SET DATA TYPE VARCHAR(6);
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test_alter_lenghth;
    ------------------------------------------------------------------------------
    
    ID          STR   
    ----------- ------
              1 abcd  
              2 efghij
              3 -     
    
      3 record(s) selected.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Trailing blanks longer than 6 would be truncated.

    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE test_alter_lenghth
    ( id  INTEGER NOT NULL PRIMARY KEY
    , str VARCHAR(80)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO test_alter_lenghth
    VALUES
      ( 1 , 'abcd' )
    , ( 2 , 'efghij' )
    , ( 3 , NULL )
    , ( 4 , 'klmn    ' )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT t.*
         , LENGTH(str) str_len
      FROM test_alter_lenghth t
    ;
    ------------------------------------------------------------------------------
    
    ID          STR                                                                              STR_LEN    
    ----------- -------------------------------------------------------------------------------- -----------
              1 abcd                                                                                       4
              2 efghij                                                                                     6
              3 -                                                                                          -
              4 klmn                                                                                       8
    
      4 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    ALTER TABLE test_alter_lenghth
    ALTER str SET DATA TYPE VARCHAR(6);
    ------------------------------------------------------------------------------
    SQL0445W  Value "klmn    " has been truncated.  SQLSTATE=01004
    
    ------------------------------ Commands Entered ------------------------------
    SELECT t.*
         , LENGTH(str) str_len
      FROM test_alter_lenghth t
    ;
    ------------------------------------------------------------------------------
    
    ID          STR    STR_LEN    
    ----------- ------ -----------
              1 abcd             4
              2 efghij           6
              3 -                -
              4 klmn             6
    
      4 record(s) selected.

Posting Permissions

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