SQL STRPOS

A Postgres-specific function doing exactly what POSITION does — function-call syntax instead of the special IN-based syntax.

What & Why

STRPOS(text, substring) is functionally identical to POSITION(substring IN text) — same result, ordinary function-call syntax, arguments in the opposite order from how POSITION reads.

See How It Works

BUSINESS QUESTION

Marketing wants to locate the at-sign in each lead email before extracting a domain.

id	campaign_id	email	created_at	qualified_at	converted_at	lead_score	source	country
301	1	ana@example.com	2024-01-21 09:10:00+00	2024-01-22 11:00:00+00	2024-02-02 10:00:00+00	86	google_ads	US
302	1	ben@example.com	2024-01-24 12:40:00+00	NULL	NULL	52	google_ads	CA
303	2	chloe@example.com	2024-02-16 08:30:00+00	2024-02-18 14:20:00+00	NULL	74	email	GB
304	3	dev@example.com	2024-03-20 17:15:00+00	2024-03-21 09:00:00+00	2024-04-04 16:00:00+00	91	linkedin	US

EXAMPLE QUERY

SELECT
  id AS lead_id,
  email,
  STRPOS(email, '@') AS at_position
FROM marketing.leads
ORDER BY lead_id;

RESULT — exact output from the displayed Queryflo rows

lead_id	email	at_position
301	ana@example.com	4
302	ben@example.com	4
303	chloe@example.com	6
304	dev@example.com	4

STRPOS returns the same actual delimiter positions as POSITION.

Now You Try

Practice this concept

Marketing wants to locate the at-sign in each lead email before extracting a domain.

Available schema

marketing

Prefix tables with marketing.table_name.

lead_idemailat_position

marketing.leads

Column	Type
id	integer
campaign_id	integer
email	text
created_at	timestamp with time zone
qualified_at	timestamp with time zone
converted_at	timestamp with time zone
lead_score	integer
source	text
country	text
archive_status	text

query.sql

Intermediate business practice