LENGTH, UPPER, LOWER

Part of the Beyond the Basics section of Coddy's SQL journey — lesson 6 of 27.

Three small functions you'll use everywhere on text columns.

LENGTH(s): number of characters in s
UPPER(s): same string in upper case
LOWER(s): same string in lower case

SELECT name, LENGTH(name) AS chars, UPPER(name) AS upper_name
FROM users

They take a string and return one. That makes them perfect for normalising messy data inside a WHERE clause: WHERE LOWER(email) = 'bob@x.com' matches 'BOB@X.COM' too.

Challenge

Easy

Available tables and columns:
countries: code, name

The code column has inconsistent casing. Return only countries whose name is between 5 and 7 characters long (inclusive), with:

code: upper-cased
name: lower-cased
name_length: character count

Order alphabetically by the upper-cased code.

Cheat sheet

Common string functions for text columns:

LENGTH(s): number of characters in s
UPPER(s): converts string to upper case
LOWER(s): converts string to lower case

SELECT name, LENGTH(name) AS chars, UPPER(name) AS upper_name
FROM users

Useful for normalising data in WHERE clauses:

WHERE LOWER(email) = 'bob@x.com'

Try it yourself

SELECT -- cleaned code, lowered name, length
FROM countries
WHERE -- name length between 5 and 7
ORDER BY -- cleaned code

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Beyond the Basics

1Conditional Logic

CASE WHEN CASE in ORDER BY COALESCE for NULLs NULLIF and IIF Recap - Attendance

4Set Operations

UNION vs UNION ALL INTERSECT EXCEPT Recap - Membership

2String Functions

LENGTH, UPPER, LOWERSUBSTR INSTR REPLACE and TRIM Concatenating with ||Recap - Invoices

5Recursive CTEs

A Number Sequence Walking a Hierarchy Recap - Org Chart

3Conditional Aggregation

COUNT with CASE SUM with CASE Pivoting Rows to Columns Recap - Traffic Sources

6Analytics Recipes

Top-N per Group Percentage of Total Running Total Keeping One Row per Key Recap - Top Customers