sqlstreak

SQL string functions

Real data is messy: inconsistent capitals, stray spaces, names in one column. String functions clean it up inside the query. Most are identical in PostgreSQL and SQLite; where they differ, the examples have a version for each.

Case and length

UPPER(), LOWER() and LENGTH() work the same in both databases.

SELECT name, UPPER(name) AS shout, LOWER(name) AS whisper, LENGTH(name) AS letters
FROM pets;

Joining text with ||

|| joins text. If any part is NULL the whole result is NULL, so wrap nullable columns in COALESCE.

SELECT name || ' the ' || species || ' (' || COALESCE(breed, 'mixed') || ')' AS label
FROM pets;

SUBSTR, REPLACE and TRIM

SUBSTR(text, start, length) cuts a piece out (positions start at 1). REPLACE(text, from, to) swaps every occurrence. TRIM() strips spaces from both ends.

SELECT name,
  SUBSTR(name, 1, 3) AS short,
  REPLACE(name, 'o', '0') AS l33t,
  TRIM('  ' || name || '  ') AS trimmed
FROM pets;

Splitting text

This is where the dialects differ. PostgreSQL has split_part(text, delimiter, n). In SQLite, find the delimiter with instr() and cut with substr().

SELECT name,
  substr(name, 1, instr(name, ' ') - 1) AS first_name,
  substr(name, instr(name, ' ') + 1) AS last_name
FROM customers;

Common mistakes

  • Concatenating a nullable column and losing the whole value.

  • Assuming positions start at 0. In SQL they start at 1.

  • Using + to join text. That's SQL Server; PostgreSQL and SQLite use ||.

Questions

Is there a CONCAT() function?+

PostgreSQL has concat(), which treats NULL as empty text. SQLite added concat() in 3.44. || works in both.

LENGTH or CHAR_LENGTH?+

In PostgreSQL both count characters. SQLite has LENGTH (characters for text).

Reading is step one. Now make it stick.

Practise this in a five-minute lesson with instant feedback. No account needed.

Practise it now