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

    Unanswered: prefix a character to field

    Hello all,

    Can someone suggest an easy way to prefix character "1" to over 100 columns of this table? Some of the character (varchar2) fields are 10 digits and some are 9. All the 9 digit fields should be prefixed by 1 and made 10.
    Thanks

  2. #2
    Join Date
    May 2003
    Posts
    58
    I figured Its simple:

    UPDATE table_name
    SET column_name = '1' || column_name
    where length(column_name) = 9;

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    I think you may be looking for something like this:

    Code:
    set heading off;
    set pagesize 1000;
    spool @change.sql;
    
    SELECT 'alter table ' || table_name || ' modify (' || 
       column_name || ' varchar2(10));'
    FROM user_tab_cols 
    WHERE data_type = 'VARCHAR2' 
       AND table_name = 'THIS_TABLE' 
       AND data_length=9;
    
    spool off;
    set heading on;
    What you get are SQL results of the form (saved in change.sql):

    Code:
    alter table THIS_TABLE modify (COLUMN1 varchar2(10));
    alter table THIS_TABLE modify (COLUMN2 varchar2(10));
    alter table THIS_TABLE modify (COLUMN3 varchar2(10));
    alter table THIS_TABLE modify (COLUMN4 varchar2(10));
    alter table THIS_TABLE modify (COLUMN5 varchar2(10));
    Check for the results of the "change.sql" that when executed it will do what you desire...and MAKE SURE you want to do this since you will be executing multiple DDL statements with the next step.

    And if so, just do this:

    Code:
    SQL> @change.sql
    Last edited by joebednarz; 04-04-06 at 17:29.
    JoeB
    save disk space, use smaller fonts

Posting Permissions

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