SQL Tutorial · Aggregation and Functions · Lesson 16 of 36

SQL String Functions

UPPER, LOWER, LENGTH, SUBSTR, REPLACE, TRIM and concatenation.

Common string functions

Example 1Try it yourself

SELECT name, UPPER(name) AS upper_name, LENGTH(name) AS len,
       SUBSTR(name, 1, INSTR(name, ' ') - 1) AS first_name
FROM customers LIMIT 4;

Output

name | upper_name | len | first_name
Ada Lovelace | ADA LOVELACE | 12 | Ada
Alan Turing | ALAN TURING | 11 | Alan
Grace Hopper | GRACE HOPPER | 12 | Grace
Linus Torvalds | LINUS TORVALDS | 14 | Linus

Concatenation

Standard SQL (and SQLite, PostgreSQL) uses || to join strings. MySQL uses CONCAT(a, b) (which PostgreSQL also supports).

Example 2Try it yourself

SELECT name || ' <' || email || '>' AS contact FROM customers WHERE country = 'UK';
SELECT REPLACE(name, ' ', '-') AS slug, TRIM('  padded  ') AS trimmed FROM categories LIMIT 2;

Output

contact
Ada Lovelace <[email protected]>
Alan Turing <[email protected]>

slug | trimmed
Books | padded
Electronics | padded

Names differ between databases

Task	SQLite	PostgreSQL	MySQL
substring	`SUBSTR(s, start, len)`	`SUBSTRING(s, start, len)`	`SUBSTRING(s, start, len)`
find position	`INSTR(s, x)`	`POSITION(x IN s)`	`LOCATE(x, s)`
length	`LENGTH(s)`	`LENGTH(s)`	`CHAR_LENGTH(s)`

Test yourself

Which operator concatenates strings in standard SQL?

+
||
&
.

Show answer

Answer: ||