Forum Stats

  • 3,828,082 Users
  • 2,260,860 Discussions
  • 7,897,469 Comments

Discussions

Oracle 12c - Sql Query - Character to Number

SANPAT
SANPAT Member Posts: 1,096 Silver Badge
edited Mar 15, 2022 7:07AM in SQL & PL/SQL

Dear Friends

I want to select the data which is in character form (34:44) which i want to convert to number as (34.44).

I tried the following Query but its giving the error message, can you please suggest the correct syntex . clock_in is in Character ("34:44")

SELECT EMP_NO, TO_NUMBER('clock_in', '99.99'), OFF_DUTY,CLOCK_IN,CLOCK_OUT FROM ATT_BIOMATRIX_DRAFT


Sanjay

Tagged:

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,428 Gold Trophy

    How can you have the hour 34 on a clock?

    Still, see if the below suits you:

    with t_clock as (

    select '22:35' hr_mi

    from dual

    )

    select to_number(replace(hr_mi,':','.')) hr_mi_num

    from t_clock

    ;

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,492 Red Diamond

    @SANPAT: I want to select the data which is in character form (34:44) which i want to convert to number as (34.44).

    It isn't clear what are you clocking. If it is hours and minutes or minutes and seconds then 34:44 is 34 + 44/60 and not 34.44. Using 34.44 will not work if you are, for example adding/subtracting clocks:

    with t_clock as (
                     select '34:44' hr_mi from dual union all
                     select '00:16' hr_mi from dual
                    )
    select  sum(replace(hr_mi,':','.')) s1,
            sum(regexp_substr(hr_mi,'[^:]+') + regexp_substr(hr_mi,'[^:]+',1,2) / 60) s2
      from  t_clock
    /
    
            S1         S2
    ---------- ----------
          34.6         35
    
    SQL>
    
    

    SY.

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy
    edited Mar 15, 2022 3:20PM

    Replacing a colon with a dot depends on NLS. For limited number of digits:

    to_number('12:34','99d99','nls_numeric_characters=:,')