UPPER

Text functions

Module 6 of 9 SQLite in your browser

Lesson concept What you'll do

1 / 4

Join, cut and change text in a query.

|| joins text, SUBSTR cuts a piece out and UPPER makes capitals.

Next: Cutting text with SUBSTR

On your own New data, same idea

Show a shelf code, the first 3 letters of the genre in capitals (Fantasy gives FAN), and the title.

The database

9 rows

book_id	title	author	genre	year	pages
1	Pride and Prejudice	Jane Austen	Romance	1813	432
2	The Great Gatsby	F. Scott Fitzgerald	Fiction	1925	180
3	Dracula	Bram Stoker	Horror	1897	418
4	Mockingjay	Suzanne Collins	Science Fiction	2010	390
5	The Time Machine	H.G. Wells	Science Fiction	1895	118
6	Angels & Demons	Dan Brown	Thriller	2000	616
7	Charlotte's Web	E.B. White	Children	1952	184
8	The Fault in Our Stars	John Green	Romance	2012	313
9	The Fellowship of the Ring	J.R.R. Tolkien	Fantasy	1954	423

Your query Ctrl + Enter runs it

Hint: click the green edit button on the board, or type it yourself

Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How UPPER, SUBSTR, || works

How do I join text?

title || ' by ' || author
|| puts pieces of text together, like & in Excel. Your own text goes in single quotes.
What do UPPER and LOWER do?

They change text to capitals or small letters: UPPER('Fantasy') gives FANTASY.
How do I cut text?

SUBSTR(genre, 1, 3) takes 3 characters starting at character 1, like MID in Excel. LENGTH(title) counts the characters, like LEN.
What else is there?

TRIM removes spaces at the ends, REPLACE(title, 'The ', '') swaps text, and INSTR(title, ' ') finds where a space is, like FIND.
What happens with NULL?

'Series: ' || series is NULL when series is NULL. Use COALESCE(series, 'none') to fill the gap first.

Other databases

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.

Joining text
Database	How	Note
SQLite (these lessons)	`title \|\| ' by ' \|\| author`
PostgreSQL	`title \|\| ' by ' \|\| author`	CONCAT(title, ' by ', author) also works and skips NULLs.
MySQL / MariaDB	`CONCAT(title, ' by ', author)`	\|\| means OR in MySQL unless PIPES_AS_CONCAT is on.
SQL Server	`title + ' by ' + author`	Or CONCAT(...) since SQL Server 2012.
Oracle	`title \|\| ' by ' \|\| author`	CONCAT takes only two pieces in Oracle.
Sybase ASE	`title + ' by ' + author`

Cutting text and counting characters
Database	How	Note
SQLite (these lessons)	`SUBSTR(genre, 1, 3), LENGTH(title)`	SUBSTRING works too since SQLite 3.34.
PostgreSQL	`SUBSTRING(genre, 1, 3), LENGTH(title)`	SUBSTR works too.
MySQL / MariaDB	`SUBSTRING(genre, 1, 3), CHAR_LENGTH(title)`	LENGTH counts bytes, so accented letters count double.
SQL Server	`SUBSTRING(genre, 1, 3), LEN(title)`	LEN ignores spaces at the end. LEFT(genre, 3) also works.
Oracle	`SUBSTR(genre, 1, 3), LENGTH(title)`
Sybase ASE	`SUBSTRING(genre, 1, 3), CHAR_LENGTH(title)`

Know someone fighting with a spreadsheet? Send them this page.