how do i cast string to integer in Oracle

Posted on 2008-11-10
Last Modified: 2012-05-05

how do i cast a string value to integer or float in oracle  ie the value

100.23 is a string field type in table A  and i need to get it into tbale b as a float value using a trigger

all help wil do
Question by:rutgermons
    LVL 6

    Accepted Solution

    you have two functions available to you .. One is convert

    select convert(datatype,value)  

    ie select convert(int,mydata)
    from test

    or you can use to_number

    select to_number(data,mask)
    from test

    The mask help determine the format of the new number.. see the following for a more detailed reference on to_number
    LVL 15

    Expert Comment

    try to use to_number function. It is the most easiest way to convert string to be number
    more function refers can goto
    SQL> select to_number('1234.56') - to_number('234.57') result from dual;

    SQL> create table abc
      2  (s varchar2(2000));
    SQL> insert into abc values ('1234.56');
    òúË 1 Ç™.
    SQL> commit;
    SQL> select to_number(s) from abc;
    SQL> select to_number(s) - 234.57 from abc;

    Open in new window


    Author Comment

    Ok thanks

    but how do i convert it using a trigger?


    Doesnt work

    all help will do

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now