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