0

create or replace FUNCTION num_check (p_string IN VARCHAR2) RETURN NUMBER IS v_new_num NUMBER; BEGIN v_new_num := TO_NUMBER(p_string); RETURN v_new_num; EXCEPTION WHEN VALUE_ERROR THEN RETURN NULL; END num_check;

Arun
  • 1

1 Answers1

0
    CREATE FUNCTION num_check(P_STRING TEXT)
    RETURN DOUBLE
IS
    v_new_num DOUBLE;
        EXIT HANDLER FOR 1366
           BEGIN
                RETURN NULL;
            END;
BEGIN
    v_new_num := CAST(REPLACE(p_string, ',', '.') AS DECIMAL (65, 30));
    RETURN v_new_num;
END num_check;
set sql_mode = DEFAULT;

The above code conversion to maria db partially works, which cannot produce value_error similar like oracle.

Oracle: SELECT num_check('994532.145188.88888888') FROM dual -- null

Maria db: SELECT num_check('994532.145188.88888888') -- 994532.145188

How to produce the same result in maria db.

Arun
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 16 '22 at 19:39