SQL POSITION

Finds WHERE a substring first occurs within a larger string, returning its 1-indexed position — or 0 if it's not found at all.

What & Why

POSITION(substring IN text) returns the 1-indexed position where substring first appears, or 0 if it doesn't appear at all. This is the natural tool whenever a fixed SUBSTRING position won't work because the split point varies row to row — like finding the @ in an email.

See How It Works

BUSINESS QUESTION

Marketing checks where the @ delimiter appears in every lead email.

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,
  POSITION('@' IN 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

POSITION returns the actual one-based @ location for each lead email.

Now You Try

Practice this concept

Marketing wants the position of the at sign in every lead email.

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