locked
Change column datatype from varchar2 to number(10) and keep exisiting data Oracle 10g RRS feed

  • Question

  • User1935524261 posted

    Hi ,

    I need to change the datatype of a varchar2 column to number(10) and keep the exisiting data.  Does anyone know if this is possible?  I appreciate all responses.

    Thursday, February 2, 2012 2:59 PM

Answers

  • User269602965 posted

    You can only store Numbers (decimal and integers) in an Oracle Number column.

    If you have numbers stored as TEXT you can convert to numbers with CAST function, or data type definition change at the table level.

    Unless T1, S, E, etc. have some numeric equivalent you derive from a lookup table, they cannot be converted to numbers.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 3, 2012 10:44 PM

All replies

  • User-1831219222 posted

    if you have data in ORALCE tables you can not simply change the data types. in order to overcome this problem do the following:

    1. ALTER TABLE: add new column (temp) with your desired datatype.
    2. UPDATE: (copy data to the new column)
    3. ALTER TABLE: drop the original column
    4. ALTER TABLE: add new column with the name of the original and the desired datatype
    5. UPDATE: (copy data to the new column from the temp column)
    6. ALTER TABLE: drop temp column

    more: http://forums.asp.net/t/1192175.aspx/1

    Thursday, February 2, 2012 3:10 PM
  • User-1407477457 posted

    if you have data in ORALCE tables you can not simply change the data types. in order to overcome this problem do the following:

    1. ALTER TABLE: add new column (temp) with your desired datatype.
    2. UPDATE: (copy data to the new column)
    3. ALTER TABLE: drop the original column
    4. ALTER TABLE: add new column with the name of the original and the desired datatype
    5. UPDATE: (copy data to the new column from the temp column)
    6. ALTER TABLE: drop temp column

    more: http://forums.asp.net/t/1192175.aspx/1

    Slight modification.  Once you finish step 3, you can simply rename the new column. 

    http://www.dba-oracle.com/t_alter_table_rename_column_syntax_example.htm

    Also, step 2 might get dicey if the data contains anything except numbers.

    Thursday, February 2, 2012 6:12 PM
  • User269602965 posted

    CAST(STRING_COLUMN AS NUMBER(10)) AS NUMBER_COLUMN

    Thursday, February 2, 2012 9:48 PM
  • User1935524261 posted

    This is the data that is currently in a varchar2 column.

    Current Values for From_Value
    T
    U
    T2
    K
    M
    A
    T
    T1
    O
    E
    S
    NSTU
    4

    I need to know if it is possible to store this data in a number(10) column and be able to add numeric data to the column going forward.

    Friday, February 3, 2012 8:51 AM
  • User-1831219222 posted

    Not much familiar with Oracle but as of SQL, I don't think you can store varchar value into number datatype. If you already have these data then you will not able to change to number.

    Friday, February 3, 2012 9:16 AM
  • User-1407477457 posted

    This is the data that is currently in a varchar2 column.

    Current Values for From_Value
    T
    U
    T2
    K
    M
    A
    T
    T1
    O
    E
    S
    NSTU
    4

    I need to know if it is possible to store this data in a number(10) column and be able to add numeric data to the column going forward.

    You'll be able to do the numeric ones.  Oracle support regular expressions so you'll be able to incorporate that logic into your where clause.  Google will help you find the correct syntax.

    Friday, February 3, 2012 9:22 AM
  • User269602965 posted

    You can only store Numbers (decimal and integers) in an Oracle Number column.

    If you have numbers stored as TEXT you can convert to numbers with CAST function, or data type definition change at the table level.

    Unless T1, S, E, etc. have some numeric equivalent you derive from a lookup table, they cannot be converted to numbers.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 3, 2012 10:44 PM