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

Text Functions

Tidy up words.

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

SQL comes with ready made tools called functions. A function takes something in, does a job, and gives something back. You've already met one, round. Now let's meet the ones that work on text.

Text Functions
From the video: Text Functions
  • Functions do one job
  • You've met ROUND
  • Now: functions for text

Capitals and length

Upper turns text into capital letters. Lower turns it into small letters. And length counts the characters, including spaces. Each function takes the column inside brackets. Run it. The Silent Orchard has eighteen characters.

query.sqlsql
SELECT UPPER(title),
       LOWER(genre),
       LENGTH(title)
FROM books
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚    UPPER(title)    โ”‚ LOWER(genre) โ”‚ LENGTH(title) โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ THE SILENT ORCHARD โ”‚ mystery      โ”‚ 18            โ”‚
โ”‚ CODE OF THE RIVER  โ”‚ thriller     โ”‚ 17            โ”‚
โ”‚ STARS OVER LAGOS   โ”‚ romance      โ”‚ 16            โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

The longest titles

Helen's website has room for about twenty characters on a book card. Which titles are too long? Length, named letters, sorted from longest. Run it. Three titles have twenty four characters. They'll need shortening on the website.

query.sqlsql
SELECT title, LENGTH(title) AS letters
FROM books
ORDER BY letters DESC
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          title           โ”‚ letters โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ A Short History of Bread โ”‚ 24      โ”‚
โ”‚ Python for Curious Minds โ”‚ 24      โ”‚
โ”‚ The Map Maker's Daughter โ”‚ 24      โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Upper, lower, and length are your first text tools. Next, joining text together, and cutting pieces out of it.

Key takeaways
  • UPPER() and LOWER()
  • LENGTH() counts characters
  • Functions take ( input )
Next up: Joining and Cutting Text โ€” || and SUBSTR