Que hace y como utilizar la función INSTR de Oracle

La función INSTR de Oracle busca una sub-cadena de caracteres dentro de una cadena de caracteres, retornando la posición de la primer ocurrencia de la misma.

Su sintaxis es:

INSTR( cadena , sub_cadena [, posicion_desde [ , ocurrencias_numero ] ] )

Sus parámetros son:

cadena: Cadena de caracteres donde se buscará la sub-cadena.

sub_cadena: Sub-cadena que se desea buscar.

posicion_desde: Posición a partir de donde se quiere buscar.

ocurrencias_numero: Número de ocurrencia que se desea encontrar su posición.

Su valor de retorno es: Número entero que contiene la posición de la primer ocurrencia de la sub-cadena dentro de la cadena, si no la encuentra retorna cero.

Ejemplos que muestra Oracle en sus manuales:

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring"

FROM DUAL;

El resultado obtenido es 14, busca la segunda ocurrencia de la cadena OR a partir de la tercer posición (R), si hubiese omitido el cuarto parámetro o introducido 1 en el mismo el resultado sería 5.

Otro ejemplo es introducir la posición a buscar en negativo, lo que hace es buscar de atrás para adelante:

SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) "Reversed Instring"

FROM DUAL;

En este caso retorna 2, ya que comienza a buscar desde la primer O de FLOOR.

Si no se le pasa ningún parámetro opcional el resultado es 2:

SELECT INSTR('CORPORATE FLOOR','OR') "Normal"

FROM DUAL;

Otra forma de utilizarlo es como un reemplazo, no muy bueno, de la operación IN. Tiene la ventaja de que puede ser utilizado en las columnas resultantes de un SELECT, y en condiciones mediante el uso de variables.

Por ejemplo de una lista de pedidos interesa separar los productos ARROZ, AZUCAR y HARINA del resto. La manera tradicional de hacerlo es utilizando un IN:

select *

from pedidos

where PRODUCTO IN ('ARROZ', 'AZUCAR', 'HARINA');

Y como alternativa:

select *

from pedidos

where INSTR('ARROZ-AZUCAR-HARINA', PRODUCTO) > 0;

Si el resultado de la función INSTR es mayor que 0 quiere decir que se encuentra. Las líneas divisorias son para separar de manea confundirse entre los productos, aunque es mas seguro también ponerle al dato a buscar …

select *

from pedidos

where INSTR('-ARROZ-AZUCAR-HARINA-', '-' PRODUCTO '-') > 0;

… en el caso de que existan productos donde su nombre esté contenido en otro producto.

Si se trata de números con largos diferentes se deberá normalizar todos valores rellenando con ceros a la izquierda:

select *

from pedidos

where INSTR('#00011#00120#15020#', '#' LPAD(codigo, 5, '0') '#') > 0;

No cabe dudas que es mejor y mas claro utilizar en la cláusula WHERE la operación IN, pero por ejemplo cuando la lista se necesita manejar en forma variable podría ser útil (alternativa a SQL dinámicos).

La razón por la que forma no es buena es por que en el caso existir un índice por el campo a buscar, Oracle no lo tomaría en cuenta y accedería a la tabla en forma secuencial (FULL), aun si no se concatenaran los separadores.

Utilizando la operación IN si utilizaría el índice.

Sí se puede utilizar cuando no se requiere acceder a la tabla por el campo a comparar, por ejemplo si existiese una índice por número de cliente y se desea saber los pedidos de estos productos realizados por el cliente 123.

select *

from pedidos

where INSTR('ARROZ-AZUCAR-HARINA', PRODUCTO) > 0 and codigo_cliente = 123;

Es más útil utilizarla en las columnas de un SELECT o asignaciones de un UPDATE combinándolo con la función DECODE (condicional).

select

SUM(DECODE(INSTR('ARROZ-AZUCAR-HARINA', PRODUCTO), 0,

0, IMPORTE_PEDIDO)) importe_que_interesa,

SUM(DECODE(INSTR('ARROZ-AZUCAR-HARINA', PRODUCTO), 0,

IMPORTE_PEDIDO, 0)) importe_que_no_interesa,

SUM(DECODE(INSTR('ARROZ-AZUCAR-HARINA', PRODUCTO), 0,

0, 1)) cantidad_que_interesa,

SUM(DECODE(INSTR('ARROZ-AZUCAR-HARINA', PRODUCTO), 0,

1, 0)) cantidad_que_no_interesa

from pedidos;

En este ejemplo primero se pregunta si el resultado de INSTR es cero (no existe) se retorna cero, de lo contrario el importe del pedido para totalizar el importe que interesa, para el que no interesa se hace lo contrario.

Si en lugar de sumar el dato del importe se suma 1, se logra contar por un lado los datos que interesan y por otro lado los demás.

Como resultado final se suman todas las filas para obtener un resumen.

En este otro ejemplo se actualiza a la tabla pedidos un campo indicando si el producto forma o no parte de los que interesan:

update pedidos

set producto_que_interesa = DECODE(INSTR('ARROZ-AZUCAR-HARINA', PRODUCTO), 0, 'No', 'Si');