INSTR

Part of the Beyond the Basics section of Coddy's SQL journey — lesson 8 of 27.

INSTR(haystack, needle) returns the 1-indexed position of needle in haystack, or 0 if it isn't there.

INSTR('alice@example.com', '@')   -- 6
INSTR('hello', 'z')               -- 0

Combined with SUBSTR it's how you split a string on a separator. To extract the part before the @ in an email:

SUBSTR(email, 1, INSTR(email, '@') - 1)

You're saying: take the email, starting from position 1, for as many characters as come before the @.

Challenge

Easy

Available tables and columns:
signups: id, email

Return id and the domain of each email (everything after the @) as domain. Assume every email contains exactly one @. Order by id.

Cheat sheet

INSTR(haystack, needle) returns the 1-indexed position of needle in haystack, or 0 if not found:

INSTR('alice@example.com', '@')   -- 6
INSTR('hello', 'z')               -- 0

Use with SUBSTR to split a string on a separator:

-- Part before '@'
SUBSTR(email, 1, INSTR(email, '@') - 1)

-- Part after '@'
SUBSTR(email, INSTR(email, '@') + 1)

Try it yourself

SELECT id,
       -- everything after the '@'
FROM signups
ORDER BY id

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

1Conditional Logic

4Set Operations

2String Functions

5Recursive CTEs

3Conditional Aggregation

6Analytics Recipes