Extract the Email Domain in SQL
Extracting the email domain takes the part after the @ sign, so you can group contacts by company or spot free-mail addresses.
Example query
This example assumes: contacts(id bigint, email text, created_at timestamptz). It is written in PostgreSQL syntax, and your table and column names will differ from these.
SELECT LOWER(SPLIT_PART(email, '@', 2)) AS domain,
COUNT(*) AS contacts
FROM contacts
WHERE email LIKE '%@%'
GROUP BY 1
ORDER BY contacts DESC
LIMIT 25;How it works
- SPLIT_PART(email, '@', 2) splits the address at the @ and returns the second piece, which is the domain.
- LOWER makes 'Acme.com' and 'acme.com' one group.
- The LIKE filter drops values that are not addresses, so malformed rows do not form a blank group.
MySQL, BigQuery, SQL Server and SQLite differences
- SPLIT_PART is PostgreSQL and Snowflake. MySQL: SUBSTRING_INDEX(email, '@', -1). SQL Server: SUBSTRING(email, CHARINDEX('@', email) + 1, 200). BigQuery: SPLIT(email, '@')[SAFE_OFFSET(1)]. SQLite: SUBSTR(email, INSTR(email, '@') + 1).
Common mistakes
- Free-mail domains (such as personal providers) are not companies; exclude a list of them before treating a domain as an account.
- Subdomains (mail.acme.com) and the main domain (acme.com) are different strings unless you normalise them.
- Values with more than one @ or with display names need cleaning first.
Ask for it in plain English
Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.
- Which email domains do most of our contacts come from?
- How many leads have a company email address rather than a free-mail one?