AI Hints
Homeโ€บSQL Tutorialโ€บWorking with Text
SQL ยท Lesson 25 of 126

Joining and Cutting Text

|| and SUBSTR.

โฑ 2 min readโ–ถ Video at 34:33๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Joining and Cutting Text
From the video: Joining and Cutting Text
  • 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.

query.sqlsql
SELECT first_name || ' ' || last_name AS name,
       SUBSTR(first_name, 1, 1) || '. ' || last_name AS short
FROM customers
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚    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.

query.sqlsql
SELECT email,
       SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM customers
WHERE email IS NOT NULL
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚         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
Next up: Replace, Trim and Missing Text โ€” clean messy text