SQL String Functions

Concept

String functions transform text values row by row. They are scalar functions: one input row produces one output value, unlike aggregates which collapse many rows into one.

Syntax

Example

SELECT first_name,
       last_name,
       CONCAT(first_name, ' ', last_name)          AS full_name,
       UPPER(LEFT(first_name, 1))                  AS initial,
       CHAR_LENGTH(first_name)                     AS name_length,
       LOWER(CONCAT(LEFT(first_name, 1), last_name)) AS suggested_login
FROM   employees
ORDER BY last_name;

-- Cleaning imported data
SELECT TRIM('   Sunrise Foods   ')            AS trimmed,
       REPLACE('Vector  Labs', '  ', ' ')     AS single_spaced,
       SUBSTRING('2024-05-27', 1, 4)           AS year_part,
       LOCATE('@', 'asha@example.com')        AS at_position,
       SUBSTRING('asha@example.com',
                 LOCATE('@', 'asha@example.com') + 1) AS domain;

Explanation

The last expression extracts a domain without any procedural code: find the position of @, then take everything after it. String positions in SQL are 1 based, not 0 based, which trips up every developer arriving from a general purpose language.

CONCAT and NULL

-- MySQL: CONCAT returns NULL if any argument is NULL
SELECT CONCAT('a', NULL, 'b');        -- NULL

-- CONCAT_WS skips NULLs and inserts a separator
SELECT CONCAT_WS(' ', 'Nikhil', NULL, 'Verma');   -- 'Nikhil Verma'

-- The standard || operator also yields NULL in PostgreSQL and Oracle,
-- except Oracle, which treats an empty string as NULL and concatenates anyway.

Important rules

• Positions are 1 based.
• CHAR_LENGTH counts characters; LENGTH in MySQL counts bytes, which differ for any non ASCII text stored as UTF-8.
• Any function wrapped around an indexed column makes the predicate non sargable. WHERE UPPER(name) = 'ASHA' cannot use an index on name.
• Case sensitivity of comparison is decided by collation, not by the functions.
• Concatenation is the least portable operation in SQL - assume it must be rewritten per dialect.

Common mistakes

• Using LENGTH() in MySQL for a character count on multi byte text.
• Building a full name with CONCAT and losing the whole value because the middle name is NULL. Use CONCAT_WS or COALESCE.
• Assuming + concatenates in MySQL. It performs numeric addition, so 'a' + 'b' is 0.
• Doing all text normalisation in queries instead of cleaning the data once on the way in.

Best practices

• Normalise on write, not on read - store the trimmed, cased value once instead of calling TRIM(UPPER(...)) in a thousand queries.
• If you must search case insensitively, use a case insensitive collation or a generated column with an index, not UPPER() in the WHERE clause.
• Use CONCAT_WS whenever any part might be NULL.
• Keep formatting for display in the application; keep the database's job to storing and finding facts.

Practice

1. Produce a column display_name of the form Nair, Asha for every employee.
2. Extract the domain from every non NULL employee email and count how many use example.com.
3. Explain why WHERE UPPER(city) = 'PUNE' is slower than WHERE city = 'Pune' on an indexed column.