La función INSTR en Oracle SQL se utiliza para localizar la posición de una subcadena dentro de una cadena de texto más grande. Esencialmente, actúa como una función de búsqueda de caracteres.

Sintaxis de la función INSTR

La función INSTR puede utilizarse en dos formatos:

1. INSTR(cadena_fuente, subcadena_buscar)
2. INSTR(cadena_fuente, subcadena_buscar [, posicion_inicio [, ocurrencia_n ]])

Donde:

• cadena_fuente: Es la cadena de texto principal donde se realizará la búsqueda.
• subcadena_buscar: Es la subcadena que se desea encontrar dentro de la cadena_fuente.
• posicion_inicio (opcional): Especifica el carácter de la cadena_fuente a partir del cual se eniciará la búsqueda. El conteo de posiciones comienza en 1. Un valor negativo indica que la búsqueda se realizará desde el final de la cadena hacia atrás.
• ocurrencia_n (opcional): Indica qué aparición de la subcadena_buscar se desea encontrar. Por defecto, es 1 (la primera aparición).

Nota: En Oracle/PLSQL, la función INSTR devuelve la posición de la primera ocurrencia de la subcadena dentro de la cadena fuente. Si no se especifican los parámetros opcionales, la búsqueda se realiza desde el inicio hasta el final de la cadena.

Ejemplos de Uso

Formato 1: Búsqueda simple

-- Buscar la primera ocurrencia de 'l' en 'helloworld'
SELECT INSTR('helloworld', 'l') FROM dual;
-- Resultado: 3

-- Buscar la primera ocurrencia de 'lo' en 'helloworld'
SELECT INSTR('helloworld', 'lo') FROM dual;
-- Resultado: 4 (la posición donde comienza 'lo')

-- Buscar la primera ocurrencia de 'wo' en 'helloworld'
SELECT INSTR('helloworld', 'wo') FROM dual;
-- Resultado: 6 (la posición donde comienza 'wo')

Formato 2: Búsqueda avanzada

-- En 'helloworld', a partir de la posición 2, buscar la 2da ocurrencia de 'l'
SELECT INSTR('helloworld', 'l', 2, 2) FROM dual;
-- Resultado: 4

-- En 'helloworld', a partir de la posición 3, buscar la 2da ocurrencia de 'l'
SELECT INSTR('helloworld', 'l', 3, 2) FROM dual;
-- Resultado: 4

-- En 'helloworld', a partir de la posición 4, buscar la 2da ocurrencia de 'l'
SELECT INSTR('helloworld', 'l', 4, 2) FROM dual;
-- Resultado: 9

-- En 'helloworld', comenzando desde el último carácter (-1), buscar la 1ra ocurrencia de 'l'
SELECT INSTR('helloworld', 'l', -1, 1) FROM dual;
-- Resultado: 9

-- En 'helloworld', comenzando desde el antepenúltimo carácter (-2), buscar la 2da ocurrencia de 'l'
SELECT INSTR('helloworld', 'l', -2, 2) FROM dual;
-- Resultado: 4

-- En 'helloworld', a partir de la posición 2, buscar la 3ra ocurrencia de 'l'
SELECT INSTR('helloworld', 'l', 2, 3) FROM dual;
-- Resultado: 9

-- En 'helloworld', comenzando desde el antepenúltimo carácter (-2), buscar la 3ra ocurrencia de 'l' (búsqueda hacia atrás)
SELECT INSTR('helloworld', 'l', -2, 3) FROM dual;
-- Resultado: 3

Comparación con MySQL

La funcionalidad de búsqueda de subcadenas que ofrece la función INSTR en Oracle es similar a la operación de LIKE en MySQL para búsquedas de patrones que incluyen comodines.

-- Equivalente en MySQL para una búsqueda simple de subcadena
-- SELECT * FROM tableName WHERE name LIKE '%helloworld%';

-- Equivalente en Oracle usando INSTR
SELECT * FROM tableName WHERE INSTR(name, 'helloworld') > 0;
-- Ambas sentencias logran el mismo resultado de búsqueda.