String Functions Compared Across Databases: Concat, Substring, Trim

1. Why string functions are so inconsistent across databases

Hardly any area of SQL shows such clear differences between database systems as string functions. While numeric functions like SUM() or AVG() are nearly identical across all major databases, text processing has historically established completely different naming conventions. The reason lies in the history: MySQL oriented itself toward C library functions, PostgreSQL followed POSIX and later the ANSI standard more closely, SQL Server inherited Transact-SQL conventions from Sybase, and Oracle developed PL/SQL largely independently with its own naming conventions.

This fragmentation affects not only function names but also fundamental semantic details like how character positions are counted, behavior with NULL inputs, and handling of multibyte characters. Anyone writing applications for multiple databases or planning a migration needs to know these string function differences in detail, since text processing in reports, search features, and data exports is extremely common, and bugs here often only become visible with specific special characters.

2. Concatenation: the || operator, CONCAT(), and + compared

PostgreSQL and Oracle follow the ANSI SQL standard and use the || operator for concatenating two strings, though the two databases react differently to NULL values: PostgreSQL returns NULL as soon as one operand is NULL, while Oracle silently treats NULL as an empty string in this operation, a common reason for differing behavior during a migration between the two systems.

MySQL only supports || in the non-default PIPES_AS_CONCAT SQL mode and by default uses the function CONCAT(a, b, ...), which accepts any number of arguments and also returns NULL when any argument is NULL. SQL Server historically uses the + operator, which can lead to implicit conversion errors with mixed string and numeric types, but since SQL Server 2012 additionally offers the more robust CONCAT() function, which automatically treats NULL values as an empty string instead of setting the entire result to NULL.

-- PostgreSQL and Oracle: ANSI-standard || concatenation operator
SELECT first_name || ' ' || last_name AS full_name FROM customers;
-- PostgreSQL: NULL propagates, Oracle: NULL treated as empty string

-- MySQL: CONCAT() function, NULL in any argument makes the whole result NULL
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

-- SQL Server: legacy + operator (implicit conversion risk) vs. modern CONCAT()
SELECT first_name + ' ' + last_name AS full_name FROM customers;   -- risky with NULLs
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;  -- NULL-safe

3. Substrings: SUBSTRING vs. SUBSTR and index conventions

All four databases support a function for substring extraction, but the function name and exact argument order differ. MySQL and SQL Server offer SUBSTRING(string, start, length), PostgreSQL supports both SUBSTRING() and the shorter SUBSTR(), and Oracle only knows SUBSTR(string, start, length) without the SUBSTRING spelling. An important commonality that is often overlooked: all four systems count character positions starting at 1, not at 0 as in most programming languages, which is a classic off-by-one mistake when porting application logic into SQL.

A subtle but important difference concerns negative length arguments and positions outside the string: MySQL and PostgreSQL return an empty string for a start position beyond the string length, while Oracle's SUBSTR() counts from the end with negative start values, a feature MySQL and PostgreSQL support similarly, but with subtly different edge-case behavior on very short strings.

-- MySQL and SQL Server: SUBSTRING(string, start, length)
SELECT SUBSTRING(sku, 1, 4) AS category_code FROM products;

-- PostgreSQL: supports both SUBSTRING() and the shorter SUBSTR()
SELECT SUBSTR(sku, 1, 4) AS category_code FROM products;

-- Oracle: only SUBSTR(), negative start counts from the end of the string
SELECT SUBSTR(sku, 1, 4) AS category_code FROM products;
SELECT SUBSTR(sku, -4) AS last_four_chars FROM products;

-- All four databases: character positions start at 1, not 0

4. Length and trimming: LENGTH, LEN, and the TRIM variants

For string length, MySQL uses LENGTH() for bytes and CHAR_LENGTH() for characters, a distinction that becomes critical with multibyte encodings like UTF-8. PostgreSQL simply calls the character-based function LENGTH() and offers OCTET_LENGTH() for byte length. Oracle also uses LENGTH() for characters, while SQL Server is the only one of the four databases that uses the abbreviated function LEN() instead of LENGTH(), a common stumbling block when switching from one of the other three databases.

For trimming whitespace or other characters, all four systems offer TRIM(), LTRIM(), and RTRIM(), but with different syntax for removing specific characters instead of plain whitespace. PostgreSQL and Oracle follow the ANSI syntax TRIM(BOTH 'x' FROM string), while MySQL supports both this syntax and the more compact TRIM('x' FROM string). SQL Server had no TRIM() at all before version 2017, only LTRIM() and RTRIM() combined, which is still commonly seen in older codebases.

-- MySQL: LENGTH() for bytes, CHAR_LENGTH() for characters
SELECT LENGTH(description), CHAR_LENGTH(description) FROM products;

-- PostgreSQL: LENGTH() for characters, OCTET_LENGTH() for bytes
SELECT LENGTH(description), OCTET_LENGTH(description) FROM products;

-- SQL Server: LEN() instead of LENGTH() — a common porting mistake
SELECT LEN(description) FROM products;

-- Trimming specific characters, ANSI syntax (PostgreSQL, Oracle, MySQL)
SELECT TRIM(BOTH '0' FROM sku) FROM products;

-- SQL Server before 2017: no TRIM(), combine LTRIM and RTRIM
SELECT LTRIM(RTRIM(description)) FROM products;

5. Case conversion and padding functions

Case conversion functions are one of the few corners where all four databases agree: UPPER() and LOWER() work identically in MySQL, PostgreSQL, SQL Server, and Oracle. Differences appear with padding functions that fill a string to a specific length. MySQL, PostgreSQL, and Oracle offer LPAD() and RPAD(), while SQL Server has no direct equivalent and instead requires a combination of RIGHT(), REPLICATE(), and concatenation to achieve the same effect.

A practical use case for padding is formatting invoice numbers or product codes with leading zeros, a pattern solved with a single LPAD() statement in MySQL, PostgreSQL, and Oracle, but requiring noticeably more code in SQL Server. Anyone migrating an application from SQL Server to one of the other three systems can often significantly simplify these workarounds.

-- MySQL, PostgreSQL, Oracle: LPAD() for zero-padding
SELECT LPAD(order_number, 8, '0') AS padded_order_number FROM orders;
-- Example: 42 becomes '00000042'

-- SQL Server: no native LPAD(), must combine RIGHT() and REPLICATE()
SELECT RIGHT(REPLICATE('0', 8) + CAST(order_number AS VARCHAR(8)), 8)
  AS padded_order_number
FROM orders;

-- UPPER() and LOWER() work identically on all four databases
SELECT UPPER(sku), LOWER(sku) FROM products;

6. Regular expressions in string functions per database

The biggest differences between the four databases show up with regular expressions. PostgreSQL offers the most complete and most POSIX-compatible support with the ~ operator and the regexp_replace() function. MySQL has supported REGEXP_LIKE(), REGEXP_REPLACE(), and REGEXP_SUBSTR() since version 8.0, with its own ICU-based regex engine that differs in details from PostgreSQL's POSIX dialect.

SQL Server had no native regex support in standard T-SQL until recently and required either CLR integration or external functions, while Oracle offers a function family similar to but not identical with PostgreSQL's syntax, with REGEXP_LIKE(), REGEXP_REPLACE(), and REGEXP_SUBSTR(). Anyone using regular expressions across multiple databases should always work with simple, portable patterns and avoid complex, engine-specific regex features like lookahead assertions, since their support varies greatly.

7. Multibyte and Unicode pitfalls in string functions

The most subtle area of bugs with string functions shows up with multibyte characters like umlauts, emoji, or Asian characters. MySQL's distinction between LENGTH() in bytes and CHAR_LENGTH() in characters becomes especially critical when processing a UTF-8 string with German umlauts: a single ä occupies two bytes in UTF-8 but exactly one character, so LENGTH() and CHAR_LENGTH() return different values, while a pure ASCII string shows identical results and the difference stays unnoticed until the first international customer data is imported.

PostgreSQL, SQL Server, and Oracle have similar pitfalls with emoji, which in SQL Server's UTF-16-based NVARCHAR are encoded as surrogate pairs of two 16-bit units, causing LEN() for a single emoji to return 2 instead of 1. During migrations between systems, any application relying on exact character counting, for example for field length validation, should explicitly test with data containing umlauts, emoji, and other multibyte characters instead of relying on pure ASCII test cases.

8. Portable wrapper strategies in application code and ORMs

For applications that must support multiple databases, it is worth building a dedicated abstraction layer for the most common string functions that generates the appropriate native syntax depending on the target database. Most ORMs like Doctrine, Eloquent, or SQLAlchemy already offer query builder methods for concatenation and substring extraction that encapsulate these differences internally, so application code never has to write CONCAT(), ||, or + directly.

For cases without an ORM, a central utility class per supported database is recommended, encapsulating the naming differences and explicitly tested with multibyte test data against all target databases in automated tests. A commonly overlooked test case: functions that behave differently on empty strings or NULL inputs should be covered in the test suite just as much as the normal case with populated values.

-- Portable concatenation pattern using COALESCE to guard against NULL
-- Works with the || operator on PostgreSQL and Oracle
SELECT COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS full_name
FROM customers;

-- Equivalent portable pattern for MySQL and SQL Server using CONCAT()
SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, ''))
  AS full_name
FROM customers;
-- Both patterns avoid NULL propagation across all four databases

9. String functions compared directly

The following table summarizes the key differences of the string functions discussed.

Anyone frequently migrating between SQL Server and the other three systems should pay particular attention to padding and length functions, since SQL Server diverges the most from the other databases here.

10. Summary

The string functions of MySQL, PostgreSQL, SQL Server, and Oracle differ significantly in function names, operators, and subtle semantic details. Concatenation ranges from CONCAT() to the || operator to the risky + in SQL Server, substring extraction varies between SUBSTRING() and SUBSTR(), and even the seemingly simple task of measuring length produces different results for multibyte characters depending on the database and function.

Anyone writing portable code for multiple databases should hide these string function differences behind an abstraction layer, consistently guard against unexpected NULL propagation with COALESCE(), and use test data with real multibyte characters instead of pure ASCII to surface Unicode pitfalls early.

11. FAQ: String Functions Compared Across Databases