Excel Text Functions in SQL: LEFT, CONCAT, UPPER

Text functions: LEFT, CONCAT, UPPER, FIND

In Excel, you'd reach for

fx=LEFT(), =MID(), =LEN(), =UPPER(), =A2&" "&B2, =SUBSTITUTE()

Most Excel text formulas have a SQL twin. The names on this site follow SQLite, which is what runs in your browser:

substr(text, start, length) is MID (and LEFT when start is 1)
length(text) is LEN
upper() and lower() are UPPER and LOWER
a || ' ' || b joins text, like & in Excel
replace(text, 'old', 'new') is SUBSTITUTE
instr(text, '@') is FIND

Try itEdit the query, then Run

name	shouting	email_domain	label
Kavya Haddad	KAVYA HADDAD	example.com	Kavya Haddad (United States)
Ben Kim	BEN KIM	example.com	Ben Kim (India)
Kavya Kim	KAVYA KIM	example.com	Kavya Kim (India)
Nikhil Tanaka	NIKHIL TANAKA	example.com	Nikhil Tanaka (Nigeria)
Elena Nguyen	ELENA NGUYEN	example.com	Elena Nguyen (United Kingdom)
Leo Garcia	LEO GARCIA	example.com	Leo Garcia (Nigeria)
Tara Ali	TARA ALI	example.com	Tara Ali (Germany)
Quinn Lopez	QUINN LOPEZ	example.com	Quinn Lopez (United States)
Sam Okafor	SAM OKAFOR	example.com	Sam Okafor (Brazil)
Ben Smith	BEN SMITH	example.com	Ben Smith (United States)

10 rows

Other databases spell a few of these differently (Postgres has left() and position(), SQL Server uses + to join text), but the ideas are identical.

Your turn

Show each customer's name and the first name only, as first_name (everything before the space).

This page on one card

Text functions: LEFT, CONCAT, UPPER, FIND: the Excel way (=LEFT()) and the SQL query, side by side — Text functions: LEFT, CONCAT, UPPER, FIND, on one card: the Excel way and the SQL. Download · Save to Pinterest

The practice data (7 tables)

customers: one row per customer
subscriptions: one row per paid plan
events: one row per thing someone did
orders: one row per order
products: a lookup table
campaigns: one row per channel per month
tickets: one row per support ticket

See every column and sample rows