1 2 Previous Next 15 Replies Latest reply: May 11, 2012 7:20 AM by Billy~Verreynne RSS

    convert char to number

    user585481
      Hi All,

      I have data in a column like " *1*2*2* " this I want to multiply and convert into a number that is the result should be 4, how to do this please help me.


      Regards

      Tulasi,

      Edited by: user585481 on May 11, 2012 12:31 AM
        • 1. Re: convert char to number
          Prabodh
          CREATE OR REPLACE FUNCTION EVAL_EXPR (
          P_IN VARCHAR2
          ) RETURNS NUMBER
          IS
            V_SQL   VARCHAR2(1000);
            V_NUM  NUMBER;
          BEGIN
           V_SQL := 'SELECT '||P_IN||' FROM DUAL;';
           EXECUTE IMMEDIATE V_SQL RETURNING V_NUM;
           RETURN V_NUM;
          END;  
          In your selects use
          SELECT ....
            EVAL_EXPR(COLUMN_A),
          .....
          FROM ....
          Regards,

          PS: In the example you have posted there are leading and trailing operators, you may have to use TRIM to get rid of them in the function.

          Edited by: Prabodh on May 11, 2012 1:11 PM
          • 2. Re: convert char to number
            hm
            You need a function to evaluate expressions.

            A quick (and dirty) solution for that is:
            create or replace 
            function evaluate (expr varchar2) return number
            as
              v_result number;
            begin
              execute immediate 'select '||expr||' from dual' into v_result;
              return v_result; 
            end;
            /
            With this you could use:
            -- Test-Data:
            with yourtable as
            (
            select '*1*2*2*' col from dual
            )
            -- Query:
            select evaluate(rtrim(ltrim(col,'*'),'*'))   -- need to trim '*' at both sides to get a valid expression!
            from yourtable;
            • 3. Re: convert char to number
              BluShadow
              SQL> ed
              Wrote file afiedt.buf
              
                1  select *
                2* from xmltable('1*2*2')
              SQL> /
              
              COLUMN_VALUE
              ----------------------------
              4
              If you are using 11g, the expression in the xmltable doesn't have to be a string literal, it can be a variable/column.
              • 4. Re: convert char to number
                MichaelS
                Also (probably only 11g):
                SQL> select dbms_xquery.eval('1*2*2') eval from dual
                /
                EVAL 
                -----
                4    
                1 row selected.
                • 5. Re: convert char to number
                  odie_63
                  Michael,

                  Which version are you running that on?

                  Thanks.
                  • 6. Re: convert char to number
                    MichaelS
                    Which version are you running that on?
                    SQL> select * from v$version where rownum = 1
                    /
                    BANNER                                                                          
                    --------------------------------------------------------------------------------
                    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production          
                    1 row selected.
                    SQL> select dbms_xquery.eval('1*5-4 div 2') eval from dual
                    /
                    EVAL 
                    -----
                    3 
                    But I just see: The whole package seems not (yet) documented :( : Oracle® Database PL/SQL Packages and Types Reference
                    • 7. Re: convert char to number
                      odie_63
                      Looks like it's not available on 11g XE, but I do see it on 11.2.0.3 too.
                      The whole package seems not (yet) documented :(
                      Well, after looking at the underlying implementation (see below), I think we can do better ourselves ;)
                      CREATE OR REPLACE PACKAGE BODY SYS.dbms_xquery AS
                      
                        FUNCTION eval(xqry varchar2) return xmltype is
                          pragma autonomous_transaction;
                          rval xmltype := null;
                        begin
                          execute immediate 'select xmlquery(:1 returning content) from dual'
                            into rval using xqry;
                          commit;
                          return rval;
                        exception
                          when others then
                            rollback;
                            raise;
                        end;
                      
                      end;
                      • 8. Re: convert char to number
                        user585481
                        Thanks for your help,

                        I tried to create this function but i am getting error as ' PLS-00103: Encounter the symbol "v_num" when expecting one of the followings : into bulk The symbol "into was substituted for "v_num to continue'

                        please help me how to do this.

                        Regards,

                        Tulasi
                        • 9. Re: convert char to number
                          user585481
                          Thanks for help.

                          I created this function but when I use this in my query I am getting following error "ORA-06575: Package or function EVALUATE is in an invalid sate"

                          Please help me to do this, this is very very urgent.

                          Regards,

                          Tulasi
                          • 10. Re: convert char to number
                            user585481
                            Hi,


                            I am running this on Oracle 10g database, version is 10.2.0.

                            Regards,

                            Tulasi
                            • 11. Re: convert char to number
                              Prabodh
                              Sorry for the typos.
                              create or replace
                              FUNCTION EVAL_EXPR (
                              P_IN VARCHAR2
                              ) RETURN NUMBER
                              IS
                                V_SQL   VARCHAR2(1000);
                                V_NUM  NUMBER;
                              BEGIN
                                V_SQL := 'SELECT '||P_IN||' FROM DUAL';
                               EXECUTE IMMEDIATE V_SQL INTO V_NUM;
                               RETURN V_NUM;
                              END;
                              Regards,
                              • 12. Re: convert char to number
                                Solomon Yakobson
                                If package dbms_aw is installed:
                                with sample_table as (
                                                      select '*1*2*2*' expr from dual
                                                     )
                                select  dbms_aw.eval_number('1' || expr || '1') result
                                  from  sample_table
                                /
                                
                                    RESULT
                                ----------
                                         4
                                
                                SQL> 
                                SY.
                                • 13. Re: convert char to number
                                  user585481
                                  How to check wether package dbms_aw is installed or not:
                                  • 14. Re: convert char to number
                                    user585481
                                    Thanks a lot,

                                    Now the function is created, but when I use this function in the SELECT statement I am getting an error as
                                    " ORA-00923: FROM keyword not found where expected
                                    ORA-06512: at "TEXPERT.EVAL_EXPR", line 10.

                                    My query is as follows:

                                    SELECT fitem_cd, fitem_cd1, sitem_cd, EVAL_EXPR(t_qty)
                                    FROM cost_bom_temp;

                                    please help me this is very urgent.

                                    Thanks & regards

                                    Tulasi
                                    1 2 Previous Next