-- Oracle 12c: Function to covert word (in english) to phone number pattern using WITH clause

WITH
 FUNCTION get_number_str(i_chr IN VARCHAR2) RETURN VARCHAR2
 IS
  v_idx    SIMPLE_INTEGER := 0;
  v_num    VARCHAR2(50);
  v_chr    VARCHAR2(1);
 BEGIN
  FOR idx IN 1 .. LENGTH(i_chr) LOOP
    v_chr := SUBSTR(i_chr,idx,1);

    IF v_chr = '(' OR v_chr = ')' OR v_chr = '[' OR v_chr = ']' OR v_chr = '-' OR  v_chr = ' ' THEN 
      v_idx := '';
    ELSE
     v_idx := 
     CASE
       WHEN v_chr BETWEEN '0' AND '9'   THEN v_chr
       WHEN v_chr IN ('a','b','c')      THEN 2
       WHEN v_chr IN ('d','e','f')      THEN 3
       WHEN v_chr IN ('g','h','i')      THEN 4
       WHEN v_chr IN ('j','k','l')      THEN 5
       WHEN v_chr IN ('m','n','o')      THEN 6
       WHEN v_chr IN ('p','q','r','s')  THEN 7
       WHEN v_chr IN ('t','u','v')      THEN 8
       WHEN v_chr IN ('w','x','y','z')  THEN 9
     ELSE
       0
     END;
    END IF;
    v_num := v_num||v_idx; 
  END LOOP;
  RETURN v_num;
 END get_number_str;
SELECT
 qt_str.v_string,
 get_number_str(qt_str.v_string) v_ph_number
FROM 
 (SELECT NVL(LOWER('&i_str'),'hello') v_string  FROM DUAL) qt_str 
;

-- This function to covert word with space (in english) to phone number pattern using WITH
-- clause

WITH
 FUNCTION get_number_string(i_chr IN VARCHAR2) RETURN VARCHAR2
 IS
  v_idx    SIMPLE_INTEGER := 0;
  v_str    VARCHAR2(50);
  v_chr    VARCHAR2(1);
 BEGIN
  FOR idx IN 1 .. LENGTH(i_chr) LOOP
    v_chr := SUBSTR(i_chr,idx,1);

    IF v_chr = '(' OR v_chr = ')' OR v_chr = '[' OR v_chr = ']' OR  v_chr = '-' OR  v_chr = ' ' THEN
      v_idx := '';
    ELSE
    ELSE
     v_idx := 
     CASE
       WHEN v_chr BETWEEN '0' AND '9'   THEN v_chr
       WHEN v_chr IN ('a','b','c')      THEN 2
       WHEN v_chr IN ('d','e','f')      THEN 3
       WHEN v_chr IN ('g','h','i')      THEN 4
       WHEN v_chr IN ('j','k','l')      THEN 5
       WHEN v_chr IN ('m','n','o')      THEN 6
       WHEN v_chr IN ('p','q','r','s')  THEN 7
       WHEN v_chr IN ('t','u','v')      THEN 8
       WHEN v_chr IN ('w','x','y','z')  THEN 9
     ELSE
       0
     END;
    v_str := v_str||v_idx; 
   END IF;
  END LOOP;
  RETURN v_str;
 END get_number_string;
SELECT
 qt_str.v_string,
 get_number_string(qt_str.v_string) v_ph_number
FROM 
 (SELECT NVL(LOWER('&i_str'),'hello') v_string  FROM DUAL) qt_str 
;