POSITION e STRPOS no SQL: achar o indice de uma substring

Quando voce precisa saber onde uma substring esta dentro de um texto — por exemplo, o @ de um email — o SQL oferece POSITION e STRPOS. Ambas retornam o indice da primeira ocorrencia e, junto com SUBSTRING, sao a ferramenta basica para analisar strings dentro da propria consulta.

Duas formas de uma mesma operacao

O PostgreSQL oferece dois jeitos de escrever uma busca de substring, equivalentes no resultado:

• POSITION(sub IN str) — a forma do padrao SQL, que se le quase como "posicao de sub em str";
• STRPOS(str, sub) — a funcao curta do PostgreSQL, com a string primeiro e o termo buscado depois.

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

A armadilha principal para quem vem de uma linguagem de programacao: o indice comeca em 1, nao em 0. Assim, para 'a@b.com' as duas funcoes retornam 2. E quando a substring nao aparece, o resultado e 0 (nao -1 nem NULL), o que e pratico para condicoes no WHERE.

Indice a partir de 1 e o valor 0

Como 0 significa "nao encontrado", ele serve bem 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;

Pegadinha: POSITION acha apenas a primeira ocorrencia e varre da esquerda para a direita. Para uma string como 'a@b@c' voce obtem a posicao do primeiro @ (valor 2); o segundo fica invisivel assim. Se str ou sub forem NULL, o resultado tambem e NULL — um motivo classico para linhas que parecem "sumir" — entao envolva colunas nullable em COALESCE(email, '').

Uso com SUBSTRING: cortando a string

A posicao sozinha raramente e o objetivo: o normal e passa-la direto para SUBSTRING e extrair um pedaco. O caso classico: dividir um email em parte local e dominio pelo @.

-- 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;

A logica e simples: POSITION(...) - 1 e o tamanho do pedaco antes do @, e POSITION(...) + 1 e o inicio logo depois. O filtro > 0 no WHERE protege das linhas sem @, onde - 1 produziria uma saida vazia ou estranha.

O mesmo truque funciona dentro de agregacoes — por exemplo, 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

O MySQL tambem aceita POSITION(sub IN str) (e padrao), mas geralmente se escreve LOCATE ou INSTR. Atencao a ordem diferente dos 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

Diferencas principais em relacao ao PostgreSQL:

• LOCATE(sub, str) coloca o termo buscado primeiro, enquanto INSTR(str, sub) e o contrario — facil de confundir;
• LOCATE aceita um terceiro argumento, a posicao inicial: LOCATE('@', email, 3) busca a partir do terceiro caractere, o que permite achar uma segunda ocorrencia;
• no ClickHouse a funcao e position(str, sub) (mesma ordem do STRPOS), alem de positionCaseInsensitive para busca sem diferenciar maiusculas.

Resumo: no PostgreSQL prefira STRPOS pela brevidade ou POSITION ... IN pela portabilidade, lembre sempre do indice a partir de 1 e do 0 quando nao ha correspondencia, e combine com SUBSTRING para ter um analisador de strings dentro do proprio SQL.