SQL SUBSTRING

What & Why

SUBSTRING(text FROM start FOR length) pulls out a piece of a string, starting at a given position (1-indexed, not 0-indexed) and running for a given number of characters. Omitting FOR length takes everything from the start position to the end of the string.

See How It Works

SELECT
  id,
  email,
  SUBSTRING(email FROM 1 FOR 4) AS email_prefix,
  SUBSTRING(email FROM POSITION('@' IN email) + 1) AS email_domain,
  SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) AS email_name
FROM marketing.leads
WHERE POSITION('@' IN email) > 0
ORDER BY id;