| How to alter the varchar column to number datatype [message #638464] |
Fri, 12 June 2015 04:50  |
Jothish
Messages: 22
Registered: February 2009
Location: Chennai
|
Junior Member |
|
|
Hi Folks,
I need some clarifications.
I have a column which is varchar2 data type and has alpha numeric values. Later point of time i want to change the column to NUMBER datatype. Anyone please let me know what are the ways it can be achieved?
Possible i guess:
Solution 1:
===========
a. We can remove the characters alone from the column and leave the number values as it is. Using TRANSLATE function.
b. Then alter the datatype to NUMBER using ALTERcommand
Solution 2: (consider that emp_id has alphanumeric characters)
===========
a. create another table as with same structure from the original table like below
create table emp_2 as (select translate(emp_id,'1234567890abcdefghijklmnopqrstuvwxyz','1234567890'), emp_name from emp
now emp_id in emp_2 will have only the number values
b. now drop the table emp (original table)
c. Then alter the datatype to NUMBER using ALTER command
d. Then rename the table name to the original table emp using ALTER command
Solution 3:
===========
a. Create view (emp) by pointing the actual table (emp_).
b. Create view emp(emp_id, emp_name) as select emp_id,emp_name from emp_. Now consider that view emp has alpha numeric values.
c. Now Alter the table emp_ by adding new column "emp_id_1" as NUMBER data type.
d. Push all the records to the new column emp_id_1 from emp_id (only numeric values using TRANSLATE function)
e. Now re create the view
create or replace view emp(emp_id, emp_name) as select emp_id_1, emp_name from emp_
f. Finally emp view will have emp_id as the numeric values alone with NUMBER data type.
These are the possible ways to achieve this. Can anyone know if any other possible ways to achieve this?
Answers appreciated,
Thanks,
Jothish. G
|
|
|
|
|
|
|
|
|
|
| Re: How to alter the varchar column to number datatype [message #638474 is a reply to message #638464] |
Fri, 12 June 2015 06:14  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
As john said, solution 1 and 2 is not possible as Oracle would throw ORA-01439: column to be modified must be empty to change datatype
If you are not bothered about the downtime, then you could add a new column as NUMBER data type, update it with required data, drop old column, rename new column with old column name.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to alter the varchar column to number datatype [message #638517 is a reply to message #638516] |
Fri, 12 June 2015 12:56 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 12 June 2015 22:46
but I bet you thank Lalit just because the other ones put the point on your faults.
Lol  Oh! Come on Michel, I don't need the "thanks". And of course, I put my point about OP's fault:
Lalit Kumar B wrote on Fri, 12 June 2015 16:44solution 1 and 2 is not possible as Oracle would throw ORA-01439: column to be modified must be empty to change datatype
By the way, OP should keep distance from the keyboard. Period!
[Updated on: Fri, 12 June 2015 13:04] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
Good luck.