Joining and Cutting Text
|| and SUBSTR.
Helen's database keeps first names and last names in separate columns. That's good design. But on an invoice, she wants the full name, together. So we need to glue text together.

- First and last names are separate
- Invoices need full names
- Glue text together
Glue with two bars
Two vertical bars join text together. First name, then a space in quotes, then the last name. Substr cuts a piece out of text. It takes the text, where to start, and how many characters. So substr of first name, one, one, is the first letter. Run it. Full names, and short names like T dot Brown.
SELECT first_name || ' ' || last_name AS name,
SUBSTR(first_name, 1, 1) || '. ' || last_name AS short
FROM customers
LIMIT 3;โโโโโโโโโโโโโโโฌโโโโโโโโโโโโ โ name โ short โ โโโโโโโโโโโโโโโผโโโโโโโโโโโโค โ Tara Brown โ T. Brown โ โ Grace Lopez โ G. Lopez โ โ Finn Haddad โ F. Haddad โ โโโโโโโโโโโโโโโดโโโโโโโโโโโโ
Find, then cut
Here's a clever combination. Instr finds where a character is. So instr of email and the at sign, gives the position of the at sign. Then substr cuts everything after it. Run it. That's the email domain. Everyone here uses mail dot com, because this is practice data.
SELECT email,
SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM customers
WHERE email IS NOT NULL
LIMIT 3;โโโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโโโโ โ email โ domain โ โโโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโโโโค โ tara.brown1@mail.com โ mail.com โ โ finn.haddad3@mail.com โ mail.com โ โ daniel.ali4@mail.com โ mail.com โ โโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโโโโ
Summary
Two bars glue text, substr cuts it, and instr finds things inside it. Next, replacing and trimming.
Key takeaways- a || b joins text
- SUBSTR(text, start, length)
- INSTR finds a character