How do you build a full-name column from first_name and last_name, and how portable is that expression?

## The standard operator SQL's concatenation operator is `||`, and it works on character strings: ```sql SELECT first_name || ' ' || last_name AS full_name FROM people; ``` That is a per-row expression like any other: it produces one derived column, and it needs an alias because a concatenation has no natural name. ## NULL propagates through the expression The most common defect in this expression has nothing to do with syntax. Under the standard, concatenating anything with NULL yields NULL, because NULL means "unknown" and an unknown component makes the whole string unknown. So a person with `last_name` NULL gets `full_name` NULL — not `'Ann '`, not `'Ann'`, but nothing at all, and the report shows an empty cell where a first name was expected. The fix is to decide, in the query, what a missing part should contribute: ```sql SELECT TRIM(COALESCE(first_name, '') || ' ' || COALESCE(last_name, '')) AS full_name FROM people; ``` `COALESCE(x, '')` substitutes an empty string for a missing part; the outer `TRIM` removes the space you would otherwise be left with at one end. Note that Oracle is a genuine exception to the propagation rule — it treats the empty string as NULL and its `||` returns the non-NULL operand — which is one more reason not to rely on an engine's default behaviour when the query has to travel. ## The operator is not universal This is one of the least portable corners of everyday SQL: - **PostgreSQL, Oracle, SQLite** implement `||` as concatenation. - **SQL Server** has no `||` operator; it concatenates with `+` and also provides `CONCAT(...)`. - **MySQL** parses `||` as logical OR under its default SQL mode; enabling `PIPES_AS_CONCAT` changes that, but MySQL code conventionally uses `CONCAT(...)`. `CONCAT` is widely available, which makes it tempting as the common denominator — but its NULL rule is itself engine-specific. Some engines return NULL if any argument is NULL, others treat a NULL argument as an empty string. Since those two behaviours produce completely different reports, an expression that must be portable should spell out its own NULL handling with `COALESCE` and not depend on which convention the engine picked. A further trap with `+` on SQL Server: because `+` is also arithmetic addition, concatenating strings that happen to look numeric — or mixing a string and a number — can turn into an addition or a conversion error rather than a concatenation. ## The neighbouring string functions The same portability logic applies to the rest of the string toolkit. The standard spellings are: - `SUBSTRING(sku FROM 1 FOR 3)` — take three characters starting at position 1. The familiar `SUBSTR(sku, 1, 3)` is common but is not the standard's form, and `LEFT`/`RIGHT` are engine extensions. - `CHARACTER_LENGTH(name)` (also spelled `CHAR_LENGTH`) — length in characters, as opposed to `OCTET_LENGTH` in bytes. - `TRIM(BOTH ' ' FROM code)` — with `LEADING` and `TRAILING` variants. - `UPPER(city)` / `LOWER(email)` — case folding, whose behaviour on non-ASCII text depends on the collation in force, so do not assume it is a pure per-character mapping. - `POSITION('-' IN sku)` — find a substring's offset. Stick to these and a query moves; reach for the friendlier local names and it stops at the engine boundary. ## Where to build the string A display name is presentation, and there is a legitimate argument for assembling it in the application, where locale rules (family name first, particles, single-name cultures) are easier to express than in a SELECT list. Building it in SQL is right when the value is used for filtering, sorting or export inside the database, or when many consumers need the same rule and you can pin it in one view. Building it in SQL and then re-implementing it slightly differently in three services is the outcome to avoid. ## Summary of the safe pattern Use `||` as the default, name the result, decide explicitly what NULL contributes with `COALESCE`, trim the seams, and if the codebase targets more than one engine, note in review that concatenation is a dialect boundary rather than assuming the operator travels.