Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    16

    Unanswered: Datatype conversion

    Hi All,

    I have a table in which i need to change the data type of a column from VARCHAR to INTEGER.
    I have data in that table.
    My request is how do i convert the VARCHAR datatype data into integer.

    Despirately in need of this.


    Thanks in Advance.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You can't do it in place. You'll have to export all data, re-create your table with the correct definition, and load the data back.

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    if you want to change the data type totally, then u have to proceed as n_i
    told. however you can work with normal arthimatic functions by just using integer function

    C:\Program Files\IBM\SQLLIB\BIN>db2 create table eone.aaaaa ( number varchar(20)
    )
    DB20000I The SQL command completed successfully.

    C:\Program Files\IBM\SQLLIB\BIN>db2 insert into eone.aaaaa values ('73'),('2938'
    )
    DB20000I The SQL command completed successfully.

    C:\Program Files\IBM\SQLLIB\BIN>db2 commit
    DB20000I The SQL command completed successfully.

    C:\Program Files\IBM\SQLLIB\BIN>db2 select integer(number) from eone.aaaaa

    1
    -----------
    73
    2938

    C:\Program Files\IBM\SQLLIB\BIN>db2 select sqrt(integer(number)) from eone.aaaaa


    1
    ------------------------
    +8.54400374531753E+000
    +5.42033209314706E+001

    --Rahul Singh

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Or, as an intermediate solution: keep your original table, but create a view which SELECTs all columns of the table, except for the VARCHAR one which it displays as CAST(colname AS INT).

    To update or insert into this view, you'll need to create stored procs which essentially update/insert into the table after data conversion CAST(INT(val) AS VARCHAR(9))
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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