--===========================================================================
-- Copywright 2002, joel crainshaw & chet west
--===========================================================================
-- DESCRIPTION
--	returns a "phone number" based on the alpha-numeric passed in
--	such as "800-CALL-NOW" returns "800-2255-669"
--	why did i dev this?  originally it was to assist in searching for
--	available toll-free numbers that had meaningful words for advertising
--===========================================================================
-- MODIFICATION HISTORY
-- Person      Date       Comments
-- ---------   ---------- -------------------------------------------
-- joel        12/??/2001 Initial Creation
--===========================================================================

CREATE OR REPLACE FUNCTION phone_words (
   p_value   IN   VARCHAR2
)
   RETURN VARCHAR2 IS
   TYPE ARRAY IS TABLE OF VARCHAR2 (40)
      INDEX BY BINARY_INTEGER;

   pad   ARRAY;
   n     VARCHAR2 (2000);
BEGIN
   pad (1) := '1111';
   pad (2) := '2abc';
   pad (3) := '3def';
   pad (4) := '4ghi';
   pad (5) := '5jkl';
   pad (6) := '6mno';
   pad (7) := '7prs';
   pad (8) := '8tuv';
   pad (9) := '9wxy';
   pad (0) := '0000';

   FOR i IN 1 .. LENGTH (p_value) LOOP
      IF    SUBSTR (LOWER (p_value), i, 1) BETWEEN 'a' AND 'z'
         OR SUBSTR (LOWER (p_value), i, 1) BETWEEN '0' AND '9' THEN
         FOR j IN 0 .. 9 LOOP
            IF INSTR (pad (j), SUBSTR (LOWER (p_value), i, 1)) <> 0 THEN
               n := n || TO_CHAR (j);
            END IF;
         END LOOP;
      ELSE
         n := n || SUBSTR (p_value, i, 1);
      END IF;
   END LOOP;

   RETURN (n);
END;
/