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.

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.

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

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))