SQL String Functions: LOWER, UPPER, TRIM and Substrings

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

LOWER and UPPER change letter case, while TRIM removes unwanted characters at the edges of text. A common cleaning expression is LOWER(TRIM(city)), which first removes surrounding spaces and then normalises case. It does not remove interior spaces or automatically correct spelling. Keep the original value available during data cleaning so an unexpected transformation can be inspected before it is written back.

Length functions do not always measure the same thing. MySQL LENGTH measures bytes and CHAR_LENGTH measures characters. SQL Server LEN excludes trailing spaces, while DATALENGTH measures storage bytes. PostgreSQL char_length measures characters and octet_length measures bytes. SQLite length on ordinary text measures characters, with special behaviour around embedded zero characters. A string that contains a multibyte character can therefore have different byte and character lengths without any database error.

Substring functions extract part of a value using a start position and sometimes a length. Positions are commonly one-based, but the function names and accepted forms differ. SQLite substr(name,1,2), PostgreSQL substring(name FROM 1 FOR 2), MySQL SUBSTRING(name,1,2) and SQL Server SUBSTRING(name,1,2) all express the same simple prefix intent. Verify negative indexes and missing-length forms rather than assuming those extensions are shared.

Concatenation also varies. PostgreSQL and SQLite support the double-pipe operator, MySQL commonly uses CONCAT, and SQL Server offers CONCAT or a plus operator with different NULL rules. CONCAT may treat NULL arguments differently from a concatenation operator. Use COALESCE explicitly when a missing component should become a chosen label, and avoid silently erasing the distinction between absent and empty text. Applying a function to an indexed column can alter index usability; store or index an approved normalised expression when repeated searches justify it.

LOWER and UPPER are shared by all four systems, but Unicode case mapping and collations differ. SQLite’s built-in LOWER and UPPER normally handle ASCII letters unless additional extensions are used. TRIM syntax for removing a specific character set and concatenation NULL rules are not uniform; test those operations on the actual engine.

Check the engine-specific syntax and version requirements in SQLite scalar string functions, PostgreSQL string functions.

Return customer names in lower case and preserve their ids. Do not update the stored names.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT id,LOWER(name) AS name_key FROM customers ORDER BY id;

id | name_key
1 | ana
2 | ben
3 | cara
4 | dev

Each selected expression produces a lower-case result value. The SELECT statement reads the table and adds the alias name_key to the output. Running a later SELECT name still returns the original capitalisation because this task contains no UPDATE.

Test UPPER('Ana'), LOWER('Ana') and TRIM(' Ana ') as independent expressions before combining them. Compare a multi-byte name’s byte and character measurements using the chosen engine’s documented functions. For SQLite, also test a non-ASCII case example to establish the default limitation. Keep that result separate from the ASCII sample, whose successful conversion proves only the simple case.