jueves, 4 de marzo de 2010

Expresiones regulares en SQL con 9i

Recibi un comentario de Laura con el siguiente problema:

"Estoy empezando a trabajar con oracle 9.2 y tengo que cambiar una sentencia que esta en mysql con expresiones regulares a oracle. La sentencia que tengo que pasar a oracle es la siguiente:

SELECT * FROM prueba WHERE (content REGEXP ?) AND (codigo = ?) AND (nombre = ?) GROUP BY mes

¿Alguien sabe como puedo pasar a oracle esta sentecia?"


A diferencia de sus sucesores, Oracle 9i tiene un limitado manejo de expresiones regulares, el cual está mas enfocado hacia PL/SQL y no tanto a SQL.

El paquete para usar expresiones regulares se llama owa_pattern. Basta hacer un desc owa_pattern en la consola para saber si tenemos acceso a el, en caso contrario solicitarlo al administrador.

La función para buscar ocurrencias en expresiones regulares dentro del paquete es:
match([cadena_entrada],[expresion_regular])
Retorna un valor booleano true o false. Y aquí está el dilema, en SQL no existe el tipo booleano como tal, por tanto el motor no 'entiende' que es lo que la función está retornando.

SQL> select owa_pattern.match('ejemplo','ej*o') from dual;

ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

La solución es crear una función traductora en PL/SQL para convertir el booleano retornado por match a un tipo válido en SQL, como por ejemplo un numérico. Este wrapper recibirá los mismos parámetros que la función original.
create or replace function sql_match (exp varchar2, regexp varchar2) return number as
begin
if (owa_pattern.match(exp, regexp)) then
return 1;
else
return 0;
end if;
end;
La ponemos en práctica:

SQL> select sql_match('ejemplo','ej.*o') res from dual;

RES
----------
1

Ahora esta función es perfectamente compatible con SQL, y podemos usarla para evaluar el valor de una columna:

Ejemplo: nombres de empleados que comienzan con 'A'
SQL> select ename, sql_match(ename,'^A.*') res from scott.emp;

ENAME RES
---------- ----------
SMITH 0
ALLEN 1
WARD 0
JONES 0
MARTIN 0
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0
ADAMS 1
JAMES 0
FORD 0
MILLER 0

14 rows selected
Ahora si, volviendo al problema original planteado por nuestra amiga, la traducción de MySQL a Oracle 9i queda:

SELECT * FROM prueba WHERE (sql_match(content, ?)=1) AND (codigo = ?) AND (nombre = ?) GROUP BY mes


Como dije anteriormente, en versiones a partir de 10g, contamos con expresiones regulares prontas para SQL, como la función REGEXP_LIKE. El ejemplo aplicado a este caso quedaría de esta forma:

SELECT * FROM prueba WHERE regexp_like(content, ?) AND (codigo = ?) AND (nombre = ?) GROUP BY mes