SQL for Normies

SQL reference

SQL SUBSTRING: cut out part of text

In Excel, you'd reach for
fx=MID(text, start, length), =LEFT(), =RIGHT()

SUBSTRING takes a slice of text: where to start (the first character is 1) and how many characters. Leave out the length to take everything to the end.

It's most useful combined with a "find" function to cut at a marker, like everything after the @ in an email.

Syntax

SUBSTRING(text, start, length)  -- start counts from 1
Try itEdit the query, then Run
emailfirst_fivedomain
kavya.haddad334@example.comkavyaexample.com
ben.kim404@example.comben.kexample.com
kavya.kim283@example.comkavyaexample.com
nikhil.tanaka266@example.comnikhiexample.com
elena.nguyen194@example.comelenaexample.com
leo.garcia59@example.comleo.gexample.com
tara.ali374@example.comtara.example.com
quinn.lopez91@example.comquinnexample.com

8 rows

In other databases

  • SQL Server: SUBSTRING(text, start, length) (length required), plus LEFT(), RIGHT(), and CHARINDEX('@', text) to find
  • MySQL / Postgres: SUBSTRING(), LEFT(), RIGHT(); find with LOCATE() (MySQL) or POSITION('@' IN text) (Postgres)
  • BigQuery: SUBSTR(text, start, length), find with STRPOS()
  • SQLite (this site): SUBSTRING or SUBSTR, find with INSTR()

Your turn

Show each order's order_date and just its month number as month_number (characters 6 and 7 of the date).

This page on one card

SQL SUBSTRING: cut out part of text: the Excel way (=MID(text) and the SQL query, side by side
SQL SUBSTRING: cut out part of text, on one card: the Excel way and the SQL. Download · Save to Pinterest
The practice data (7 tables)

See every column and sample rows