For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 Connected as hr SQL> SQL> with data as( 2 select 12.456 as val from dual) 3 select val - trunc(val) from data; VAL-TRUNC(VAL) -------------- 0,456 SQL>
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 Connected as hr SQL> SQL> with data as( 2 select 12.456 as val from dual) 3 select to_number(regexp_replace(to_char(val), '^[0-9]+\.', '')) from data; TO_NUMBER(REGEXP_REPLACE(TO_CH ------------------------------ 456 SQL>
SELECT TO_NUMBER (SUBSTR ('33.455', INSTR ('33.455', '.', 1, 1) + 1)) mydecimal FROM DUAL;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 Connected as hr SQL> SQL> with data as( 2 select 3.56 as val from dual) 3 select to_number(regexp_replace(to_char(val), '^[0-9]+\.', '')) from data; TO_NUMBER(REGEXP_REPLACE(TO_CH ------------------------------ 56 SQL> SQL> SELECT TO_NUMBER (SUBSTR ('3.56', INSTR ('3.56', 2 '.', 3 1, 4 1) 5 + 1)) mydecimal 6 FROM DUAL; MYDECIMAL ---------- 56 SQL>
SELECT TO_NUMBER (SUBSTR ('33.455', INSTR ('33.455', '.', 1, 1) )) mydecimal FROM DUAL; MYDECIMAL ---------- .455 or SELECT TO_NUMBER (SUBSTR ('33.455', INSTR ('33.455','.',1,1)+1 )) mydecimal FROM DUAL; MYDECIMAL ---------- 455
SQL> select fnc_get_decimal_part(3.56) from dual; FNC_GET_DECIMAL_PART(3.56) -------------------------- 56
CREATE OR REPLACE FUNCTION fnc_get_decimal_part(p_number IN NUMBER) RETURN NUMBER IS BEGIN RETURN to_number(regexp_replace(to_char(p_number), '^[0-9]+\.', '')); END fnc_get_decimal_part;
DECLARE v_number NUMBER; BEGIN SELECT TO_NUMBER (SUBSTR ('33.455', INSTR ('33.455', '.', 1, 1) + 1)) mydecimal INTO v_number FROM DUAL; DBMS_OUTPUT.put_line (v_number); END;
CREATE OR REPLACE FUNCTION rdecimal (i_nbr IN NUMBER) RETURN NUMBER AS v_number NUMBER; v_nbr VARCHAR2 (40); BEGIN v_nbr := TO_CHAR (i_nbr); SELECT TO_NUMBER (SUBSTR (v_nbr, INSTR ('33.455', '.', 1, 1) + 1)) mydecimal INTO v_number FROM DUAL; -- DBMS_OUTPUT.put_line (v_number); RETURN v_number; END; select rdecimal(33.455) from dual;
Hi to all,
I think the best way (with better performance) is using mathematical function "mod", with the second parameter = "1". For example:
>select mod(3.1416, 1) as frac_part from dual;
FRAC_PART
----------
,1416
1 row selected.
Regards,
Do you think it's a good idea to drag up a thread that is nearly 5 years old?
You're right that MOD can be used to answer this question, but the OP is surely not sitting around still waiting for more answers, unlike the people who have asked questions recently that still need answering.
DO NOT drag up old threads unnecessarily.
Locking this thread
@"user5154192"Reporting abuse on a reasonable moderator action is a bit silly, no?