-- A couple PL/SQL procedures to enable translation of words to the 
-- equivalent number sequences when spelled out on a telephone
-- keypad.
--
-- You may use this software free under the GNU General Public License.
--
-- eveander@eveander.com, February 24, 2001


-- Translates letters into keypad numbers (e.g., a, b, and c correspond to 2).

create or replace function vxml_keypad_number (v_letter IN varchar)
return varchar
IS
  v_number    char(1);
BEGIN
  select decode(lower(v_letter),'a',2,'b',2,'c',2,'d',3,'e',3,'f',3,'g',4,'h',4,'i',4,'j',5,'k',5,'l',5,'m',6,'n',6,'o',6,'p',7,'q',7,'r',7,'s',7,'t',8,'u',8,'v',8,9) into v_number from dual;
  RETURN v_number;
END vxml_keypad_number;
/
show errors


-- Translates an alphabetic string into the equivalent keypad numbers.

create or replace function vxml_keypad_string (v_string IN varchar)
return varchar
IS
  v_keypad_string    varchar(200);
  v_string_length    integer;
  i                  integer;
  v_curr_char        char(1);
BEGIN
  v_keypad_string := null;
  select length(v_string) into v_string_length from dual;
  FOR i in 1..v_string_length LOOP
    select vxml_keypad_number(substr(v_string,i,1)) into v_curr_char from dual;
    v_keypad_string := v_keypad_string || v_curr_char;
  END LOOP;
  RETURN v_keypad_string;
END vxml_keypad_string;
/
show errors


-- A funtional index on the ACS 3.x users table in order to speed up the execution
-- of the above function.
-- This only works with Oracle 8.i and better.  But don't despair if it
-- doesn't work; it's not required.

CREATE INDEX vxml_keypad_string_index ON users (vxml_keypad_string(last_name));