POSITION y STRPOS en SQL: encontrar el indice de una subcadena

Cuando necesitas saber donde esta una subcadena dentro de un texto — por ejemplo, la @ de un correo — SQL te ofrece POSITION y STRPOS. Ambas devuelven el indice de la primera coincidencia, y junto a SUBSTRING son la herramienta basica para analizar cadenas dentro de la propia consulta.

Dos formas de una misma operacion

PostgreSQL ofrece dos maneras de escribir una busqueda de subcadena, equivalentes en resultado:

• POSITION(sub IN str) — la forma del estandar SQL, que se lee casi como "posicion de sub en str";
• STRPOS(str, sub) — la funcion corta de PostgreSQL, con la cadena primero y lo buscado despues.

-- Both return the index of '@' inside the email
SELECT
  email,
  POSITION('@' IN email) AS pos_standard,
  STRPOS(email, '@')     AS pos_shorthand
FROM users;

La trampa principal para quien viene de un lenguaje de programacion: el indice empieza en 1, no en 0. Asi, para 'a@b.com' ambas funciones devuelven 2. Y cuando la subcadena no aparece, el resultado es 0 (no -1 ni NULL), lo que resulta comodo para condiciones en WHERE.

Indice desde 1 y el valor 0

Como 0 significa "no encontrado", sirve muy bien como filtro:

-- Users whose email has no '@' at all (likely bad data)
SELECT id, email
FROM users
WHERE STRPOS(email, '@') = 0;

-- Users with a real address: '@' is present
SELECT id, email
FROM users
WHERE POSITION('@' IN email) > 0;

Gotcha: POSITION solo encuentra la primera coincidencia y recorre de izquierda a derecha. Para una cadena como 'a@b@c' obtienes la posicion de la primera @ (valor 2); la segunda queda invisible asi. Si str o sub son NULL, el resultado tambien es NULL — un motivo clasico de filas que parecen "desaparecer" — asi que envuelve las columnas nullables en COALESCE(email, '').

Combinar con SUBSTRING: cortar la cadena

La posicion por si sola rara vez es el objetivo: lo normal es pasarla directo a SUBSTRING para extraer un trozo. El caso clasico: dividir un correo en parte local y dominio por la @.

-- Split email into local part and domain
SELECT
  email,
  SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1)        AS local_part,
  SUBSTRING(email FROM POSITION('@' IN email) + 1)              AS domain
FROM users
WHERE POSITION('@' IN email) > 0;

La logica es simple: POSITION(...) - 1 es la longitud del trozo antes de la @, y POSITION(...) + 1 es el inicio justo despues. El filtro > 0 en WHERE protege de las filas sin @, donde - 1 daria una salida vacia o extrana.

El mismo truco funciona dentro de agregados — por ejemplo, contar pedidos por dominio de usuario:

-- Order counts grouped by email domain
SELECT
  SUBSTRING(u.email FROM POSITION('@' IN u.email) + 1) AS domain,
  COUNT(o.id) AS orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE POSITION('@' IN u.email) > 0
GROUP BY domain
ORDER BY orders DESC;

MySQL: LOCATE e INSTR

MySQL tambien admite POSITION(sub IN str) (es estandar), pero suele escribirse LOCATE o INSTR. Cuidado con el orden distinto de los argumentos:

-- MySQL: same 1-based result, 0 when not found
SELECT
  LOCATE('@', email) AS by_locate,   -- needle first, then haystack
  INSTR(email, '@')  AS by_instr;    -- haystack first, then needle

Diferencias clave frente a PostgreSQL:

• LOCATE(sub, str) pone lo buscado primero, mientras que INSTR(str, sub) es al reves — facil de confundir;
• LOCATE acepta un tercer argumento, la posicion de inicio: LOCATE('@', email, 3) busca desde el tercer caracter, lo que permite hallar una segunda aparicion;
• en ClickHouse la funcion es position(str, sub) (mismo orden que STRPOS), ademas de positionCaseInsensitive para buscar sin distinguir mayusculas.

En resumen: en PostgreSQL usa STRPOS por brevedad o POSITION ... IN por portabilidad, recuerda siempre el indice desde 1 y el 0 cuando no hay coincidencia, y combinalo con SUBSTRING para tener un analizador de cadenas dentro del propio SQL.