SQL / TEXT, NUMERIC, AND DATE FUNCTIONS
Concatenating and measuring strings
Assemble text with ||, concat and concat_ws, stop a single NULL from blanking a whole label, and measure strings in characters instead of bytes.
What you will learn
- Concatenate with || and expect NULL out whenever any operand is NULL
- Use concat_ws to skip missing parts and their separators in one step
- Count characters with char_length and bytes with octet_length
- Know that char_length(NULL) is NULL, so length(col) = 0 never matches NULL rows
Understanding Concatenating and measuring strings
Concatenation is an operator, not a statement: first_name || ' ' || last_name is evaluated once per row and produces a new text value that nothing stores. The double pipe is the SQL standard spelling and works in PostgreSQL, SQLite, Oracle and DB2; MySQL needs CONCAT() unless the server runs with PIPES_AS_CONCAT, and SQL Server spells it +. Because it is an ordinary operator it obeys the same NULL rule as arithmetic, so an unknown operand makes the whole result unknown, which is why one missing middle name blanks an entire label instead of shortening it.
That NULL rule is why three tools exist instead of one. concat() in PostgreSQL and SQL Server treats NULL as an empty string, while MySQL's CONCAT() returns NULL if any argument is NULL; concat_ws() goes one step further and drops both the NULL argument and the separator that would have sat in front of it, which is exactly what an optional middle part needs. Reach for coalesce(col, '') only when you are placing the separators yourself and are ready for the double space or dangling comma it leaves behind.
Measuring looks simpler than it is, because "how long is this string" hides three questions: how many characters, how many bytes, and how wide it prints. char_length() answers the first and octet_length() the second, and the two diverge as soon as a value leaves ASCII, since 'jalapeño' is 8 characters but 9 bytes in UTF-8. Dialect names blur the distinction: MySQL's LENGTH() counts bytes while CHAR_LENGTH() counts characters, and SQL Server's LEN() ignores trailing spaces while DATALENGTH() does not, so choose the function that matches the question you are actually asking.
One more consequence of treating these as expressions: char_length(NULL) is NULL, not 0, so a filter like WHERE length(notes) = 0 quietly skips every row where notes is missing.
SELECT first_name || ' ' || last_name AS full_name,
length(first_name || ' ' || last_name) AS n_chars,
first_name || ' ' || nickname AS naive,
concat_ws(' ', first_name, nickname, last_name) AS safe_name
FROM (VALUES ('Grace', 'Hopper', 'Amazing'),
('Ada', 'Lovelace', NULL)) AS p(first_name, last_name, nickname);Concatenation and length are value expressions, so they inherit SQL's NULL propagation and the character-versus-byte distinction; the fix is picking the right function, not writing more pipes.
Worked examples
Characters are not bytes
Shows how the same literal measures differently depending on which question the function answers.
SELECT char_length('jalapeño') AS chars,
octet_length('jalapeño') AS bytes,
char_length(NULL::text) AS null_len,
char_length('') AS empty_len;Example explained
Line 1char_length counts characters, so the eight letters of 'jalapeño' give 8 whatever the encoding is.
Line 2octet_length counts stored bytes, and in a UTF-8 database 'ñ' needs two of them, so the same literal measures 9.
Line 3char_length(NULL::text) is NULL rather than 0; the cast only tells PostgreSQL which overload of the function to use.
Line 4char_length('') is 0, which is why an empty-string test and a NULL test are two different checks.
Non-text operands and NULL guards
Compares || , concat and coalesce on the same missing value, and shows what happens when a number is concatenated.
SELECT 'Order #' || 1001 AS label,
'a' || NULL || 'b' AS piped,
concat('a', NULL, 'b') AS concatenated,
'a' || coalesce(NULL, '') || 'b' AS guarded;Example explained
Line 1'Order #' || 1001 works because PostgreSQL has a text || anynonarray operator that converts the number; 1001 || 2002 fails, since neither side is text.
Line 2'a' || NULL || 'b' is NULL because the operator has no basis for guessing what the missing piece was.
Line 3concat('a', NULL, 'b') returns 'ab' since concat substitutes an empty string for NULL in PostgreSQL, whereas MySQL's CONCAT would return NULL here.
Line 4coalesce(NULL, '') makes that substitution explicit and is the portable way to write the same fix.
Length as a data check
Uses length in a WHERE clause to find codes that are not exactly five characters.
SELECT code, length(code) AS len
FROM (VALUES ('AB123'), ('AB12'), ('AB123 ')) AS t(code)
WHERE length(code) <> 5;Example explained
Line 1length is PostgreSQL's alias for char_length, so 'AB123' scores 5 and the WHERE clause removes it.
Line 2'AB123 ' scores 6 because a trailing space is a character like any other; the printed table cannot show that space, but the count can.
Line 3Wrapping the column in length() means a plain index on code cannot be used; an expression index on length(code) would be needed for a large table.
Important notes
Fixed-length CHAR(n) columns are blank-padded and engines disagree on whether the padding counts: SQL Server's LEN('ab ') is 2 while DATALENGTH is 3, and Oracle's LENGTH on a CHAR(10) holding 'ab' returns 10.
In Oracle the empty string is NULL, so LENGTH('') returns NULL rather than 0, and a length(col) = 0 test written against PostgreSQL finds nothing there.
Common mistakes
Expecting 'A' || NULL to yield 'A': in PostgreSQL, SQLite and standard SQL the whole label becomes NULL, so the row prints blank and any LIKE filter on that label silently drops it. Oracle is the exception, which is where the habit comes from.
Patching NULLs with coalesce(middle_name, '') while keeping hand-written separators, which leaves 'Ada Lovelace' with a double space or 'Berlin, ' with a dangling comma; concat_ws removes the orphaned separator too.
Enforcing a character limit with MySQL's LENGTH(), which counts bytes: 'jalapeño' measures 9 and 'München' fails a seven-character check. CHAR_LENGTH() is the character count.
Try it yourself
Change, predict, then run
In a PostgreSQL browser editor, build a three-row VALUES list of first, middle and last names with one NULL middle name, then produce a full_name column that is never NULL alongside its char_length. Confirm the row with the missing middle name contains exactly one space.
Open the SQL workspaceCheck your understanding
A report builds a location label as city || ', ' || region. Rows whose region is NULL come out blank, but concat_ws(', ', city, region) shows just 'Berlin' for the same row. What explains the difference?
- || returns NULL only when every operand is NULL, so city must have been empty as well
- concat_ws substitutes an empty string for NULL, so the result is really 'Berlin, ' with the comma still attached
- || propagates NULL because a value concatenated with an unknown is still unknown, while concat_ws ignores NULL arguments together with the separator that would have preceded them
- char_length(NULL) is 0, so the second operand contributes nothing and the || result is truncated
Show answer
|| follows the same NULL rule as arithmetic: one unknown operand makes the entire expression unknown, which is why the label vanishes rather than shortening. Option 2 is the tempting one, but concat_ws drops the separator belonging to a skipped argument as well, which you can prove with char_length(concat_ws(', ', 'Berlin', NULL)) returning 6, the length of 'Berlin' alone.