Extra topic · The word wizard

SQL String Functions: UPPER, SUBSTR, CONCAT, REPLACE

Changing text inside a query: upper and lower case, length, cutting a piece out, joining strings together and replacing words.

Covers: UPPER · LOWER · LENGTH · SUBSTR · CONCAT · REPLACE

Practise Text Functions

The lesson

Every example runs against the same small pizza shop database: pizzas, customers, orders and staff.

# Text functions Text functions change text inside a query: put names in one case, cut out a part, join pieces together. You need them when data is messy, or has to be shown in a different shape. A function takes a value and returns a new one. - `UPPER(name)` in capital letters - `LOWER(name)` in small letters > MySQL text comparisons usually ignore case already, so 'leeds' = 'Leeds' is true. LOWER matters in other databases and in your app.

SELECT name,
  UPPER(name), LOWER(name)
FROM customers

# The length of a text - `LENGTH(name)` the length in bytes - `CHAR_LENGTH(name)` the length in characters For plain English letters they're the same. A letter like é takes 2 bytes, so LENGTH('José') is 5. > Use CHAR_LENGTH to check against a limit like VARCHAR(50): the 50 counts characters, not bytes.

SELECT name, LENGTH(name)
FROM pizzas

# Cut out part of a text: SUBSTR - `SUBSTR(city, 1, 3)` start at character 1, take 3 Counting starts at 1, not 0. > Leave out the last number to take everything to the end: SUBSTR(email, 5).

SELECT city, SUBSTR(city, 1, 3)
FROM customers

# Join texts: CONCAT - `CONCAT(name, ' aka ', nickname)` joins the pieces into one text If any piece is NULL, the whole result is NULL: Bob has no nickname, so he gets NULL. > Wrap pieces that may be empty: CONCAT(name, ' aka ', COALESCE(nickname, '-')). And don't use || to join text in MySQL: there it means OR.

SELECT name,
  CONCAT(name, ' aka ', nickname)
FROM customers

# Replace and trim - `REPLACE(email, 'mail', 'pizza')` swaps every 'mail' for 'pizza' - `TRIM(text)` removes the spaces from both ends > TRIM what users type before you store or compare it. ' dev@mail.com', with a space, won't match.

SELECT email,
  REPLACE(email, 'mail', 'pizza')
FROM customers

# Functions in WHERE, and inside each other Functions work in WHERE too. When one function is inside another, the inner one runs first. - `WHERE LENGTH(name) > 10` only names longer than 10 characters > As with dates, a function around a column in WHERE can't use an index on that column.

SELECT name
FROM pizzas
WHERE LENGTH(name) > 10

Appian corner

In Appian these are upper(), lower(), len(), mid(text, start, count), substitute(), trim(), and & to join text. They are 1-based too.

Practice questions

Try each one before opening the answer. To write and run them against the database in your browser, practise the level.

1. Every customer's name in capital letters.
```
SELECT ___(name) FROM customers
```
Show the answer
```
SELECT UPPER(name) FROM customers
```
The shouting function.
2. What does this return?
```
SELECT LENGTH('Pizza')
```
- 4
- 5
- 6
- 'Pizza'
Show the answer
5. P-i-z-z-a is 5 characters.
3. Pizza names that are longer than 10 characters, with their length.
Show the answer
```
SELECT name, LENGTH(name)
FROM pizzas
WHERE LENGTH(name) > 10
```
WHERE LENGTH(name) > 10
4. A greeting for every customer, like 'Hi Alice Brown!', in a column called greeting.
Show the answer
```
SELECT CONCAT('Hi ', name,
  '!') AS greeting
FROM customers
```
CONCAT('Hi ', name, '!') AS greeting
5. What does this give for Alice in MariaDB, which Appian Cloud runs on?
```
SELECT 'Hi ' || name FROM customers
```
- 'Hi Alice Brown'
- 0
- An error
- NULL
Show the answer
0. || means OR in MySQL and MariaDB, and neither piece counts as true, so it gives 0. Glue text with CONCAT().
6. This should show the first 3 letters of each city, but the cities come out too short. Fix it.
```
SELECT SUBSTR(city, 0, 3)
FROM customers
```
Show the answer
```
SELECT SUBSTR(city, 1, 3)
FROM customers
```
SQL counts from 1, not 0.
7. Web addresses for the menu: every pizza name in lowercase with its spaces turned into -, in a column called slug.
Show the answer
```
SELECT REPLACE(LOWER(name), ' ',
  '-') AS slug
FROM pizzas
```
REPLACE(LOWER(name), ' ', '-') AS slug
8. Boss question! Every email in lowercase, with '@mail.com' replaced by '@pizza.io'.
Show the answer
```
SELECT REPLACE(LOWER(email),
  '@mail.com', '@pizza.io')
FROM customers
```
Put LOWER inside REPLACE: REPLACE(LOWER(email), '@mail.com', '@pizza.io'). Bob shouts his email.

Practise Text Functions