Procedure SOUNDEX en español

[Dario Nasta]

Hola,

Estuve intentando convertir con éxito un grupo de procedimientos para implementar SOUNDEX en español, el grupo de procedimientos lo obtuve de un pkg para Oracle, pero cuando lo ejecuto con algunas palabras me retorna nul, por ej:
Select soundesp("proposición")
soundesp("metáfora")
etc.

El código es un poco extenso, alguien me puede ayudar a encontrar el error?

Muchas gracias,

Este es el código:

CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `soundesp`(`p_pal` TEXT)
RETURNS text
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN

DECLARE pri VARCHAR(1);
DECLARE subcadena TEXT;
DECLARE ret TEXT;

-- 1. eliminar letra h a la izquierda, acentos, enie
SET ret = SUBSTRING(soundesp_eli_acc(p_pal), 1, 30);
-- 2. asociar letras foneticamente parecidas para la primera letra de la palabra
SET ret = soundesp_com_pal(ret);
-- 3. simplificar combinaciones dobles
SET ret = soundesp_cnv_dos(ret);
-- 4. retener la primera letra
SET pri = SUBSTRING(ret, 1, 1); -- K ( KUARENTA )
-- 5. tomar la subcadena derecha
SET subcadena = SUBSTRING(ret, 2, length(ret));
-- 6. eliminar vocales foneticas
SET subcadena = soundesp_eli_let(subcadena);
-- 7. mapeo letras foneticamente equivalentes a numeros
SET subcadena = soundesp_map_num(subcadena);
-- 8. elimino numeros iguales adyacentes
SET subcadena = soundesp_eli_ady(subcadena);
-- 9. retorno
SET ret = CONCAT(pri, subcadena);
IF LENGTH(ret) < 4 THEN
SET ret = RPAD(ret, 4, '0');
END IF;

RETURN ret;


END

//##############################################################

CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `soundesp_cnv_dos`(`p_pal` TEXT)
RETURNS text
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN

DECLARE ret TEXT;

SET ret = REPLACE(p_pal, "CH", "V");
SET ret = REPLACE(ret, "QU", "K");
SET ret = REPLACE(ret, "LL", "J");
SET ret = REPLACE(ret, "CE", "S");
SET ret = REPLACE(ret, "CI", "S");
SET ret = REPLACE(ret, "YA", "J");
SET ret = REPLACE(ret, "YE", "J");
SET ret = REPLACE(ret, "YI", "J");
SET ret = REPLACE(ret, "YO", "J");
SET ret = REPLACE(ret, "YU", "J");
SET ret = REPLACE(ret, "GE", "J");
SET ret = REPLACE(ret, "GI", "J");
SET ret = REPLACE(ret, "NY", "N");

RETURN ret;
END

//##############################################################

CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `soundesp_com_pal`(`p_pal` TEXT)
RETURNS text
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN

DECLARE pri_letra VARCHAR(1);
DECLARE resto TEXT;
DECLARE letra_ret VARCHAR(1);

SET pri_letra = SUBSTRING(p_pal, 1, 1);
SET resto = SUBSTRING(p_pal, 2);
SET letra_ret = pri_letra;

IF pri_letra = "V" THEN
SET letra_ret = "B";
ELSEIF pri_letra = "X" THEN
SET letra_ret = "S";
ELSEIF pri_letra = "Z" THEN
SET letra_ret = "S";
ELSEIF (pri_letra = "G" AND (SUBSTRING(p_pal, 2, 1) IN ("E", "I"))) THEN
SET letra_ret = "J";
ELSEIF (pri_letra = "C" AND SUBSTRING(p_pal, 2, 1) NOT IN ("H", "E", "I")) THEN
SET letra_ret = "K";
ELSE
SET letra_ret = NULL;
END IF;

RETURN CONCAT( letra_ret, resto );
END

//##############################################################

CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `soundesp_eli_acc`(`p_pal` TEXT)
RETURNS text
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN

RETURN REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( UPPER(p_pal), "Ñ", "N" ), "Á", "A"), "É", "E" ), "Í", "I" ), "Ó", "O"), "Ú", "U");

END

//##############################################################

CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `soundesp_eli_ady`(`p_num` TEXT)
RETURNS text
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN

DECLARE ant VARCHAR(1);
DECLARE act VARCHAR(1);
DECLARE ret_num TEXT;
DECLARE itrt INT;

SET ant = SUBSTRING(p_num, 1, 1);
SET ret_num = ant;

IF ( p_num IS NOT NULL ) AND ( length(p_num) > 1 ) THEN

SET itrt = 2;

WHILE itrt <= LENGTH(p_num) DO

SET act = substr(p_num, itrt, 1);

IF act <> ant THEN
SET ret_num = CONCAT( ret_num, act);
SET ant = act;
END IF;

SET itrt = itrt + 1;

END WHILE;

SET ret_num = SUBSTRING(ret_num, 1, 3);

ELSEIF LENGTH(p_num) = 1 THEN
SET ret_num = p_num;
ELSE
SET ret_num = NULL;
END IF;

RETURN ret_num;
END

//##############################################################

CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `soundesp_eli_let`(`p_pal` TEXT)
RETURNS text
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN

RETURN REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( p_pal, "@", "@" ), "A", "@" ), "E", "@" ), "I", "@" ), "@O", "@" ), "U", "@" ), "H", "@" ), "W", "@" ), "Y", "@" );

END

//##############################################################

CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `soundesp_map_num`(`p_pal` TEXT)
RETURNS text
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN

RETURN REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( p_pal, "B", "1" ), "P", "1" ), "F", "1" ), "V", "1" ), "C", "2" ), "G", "2" ), "K", "2" ), "S", "2" ), "X", "2" ), "Z", "2" ), "D", "3" ), "T", "3" ), "L", "4" ), "M", "5" ), "N", "5" ), "R", "6" ), "Q", "7" ), "J", "7" );

END

//##############################################################