SQL · Guides · October 30, 2026 · 10 min read

SQL string functions: CONCAT, SUBSTRING, REPLACE, UPPER, TRIM and more

String functions are how you clean and reshape text inside a query instead of exporting to a spreadsheet. A handful covers most real work: || and CONCAT glue text together, SUBSTRING, LEFT, RIGHT and SPLIT_PART cut pieces out, REPLACE and REGEXP_REPLACE swap characters, UPPER, LOWER and TRIM normalise values so they match, and LENGTH and POSITION measure and search. This guide goes through each one on real data, and points out where PostgreSQL, DuckDB and other databases disagree.

Every query here runs on the airline database from the Nomto SQL course, in PostgreSQL syntax on DuckDB (the engine behind the Nomto SQL room). The result tables are the real output. The text columns we will use:

Concatenation: || and CONCAT

In PostgreSQL and DuckDB the standard way to join strings is the || operator. CONCAT() does the same job as a function:

SELECT airport_code,
       city,
       airport_name,
       city || ' (' || airport_code || ')' AS label,
       CONCAT(city, ' - ', airport_name) AS full_name
FROM airports
WHERE city IN ('Moscow', 'Sochi')
ORDER BY airport_code;

The two are not quite equal. They treat NULL differently:

SELECT 'PG' || NULL AS pipes,
       CONCAT('PG', NULL) AS concat_fn,
       concat_ws(' / ', 'SVO', NULL, 'LED') AS ws;

With ||, one NULL turns the whole result into NULL. CONCAT skips NULLs. concat_ws (concatenate with separator) puts the separator between values and also skips NULLs, which is handy for building addresses or route labels from optional parts. If you build a label with || and some rows come out empty, a NULL column is the usual reason; wrap it in COALESCE(col, '') or switch to CONCAT.

Other databases. || and CONCAT both work in PostgreSQL, DuckDB, Oracle and SQLite. MySQL uses CONCAT (there || means logical OR unless a mode is switched on), and SQL Server uses + or CONCAT.

UPPER, LOWER and capitalising names

UPPER and LOWER change case. Passenger names are stored in capitals:

SELECT passenger_name,
       lower(passenger_name) AS lower_name,
       upper(left(split_part(passenger_name, ' ', 1), 1))
         || lower(substring(split_part(passenger_name, ' ', 1), 2)) AS first_name,
       length(passenger_name) AS len
FROM tickets
LIMIT 3;

PostgreSQL has initcap(passenger_name), which capitalises every word in one go (Valeriy Tikhonov). DuckDB does not have initcap, so the query above builds it by hand: first letter upper case, the rest lower case.

The bigger use of LOWER is matching. String comparison is case-sensitive: WHERE city = 'moscow' finds nothing. Compare lower(city) = 'moscow', or use ILIKE, which is LIKE without case:

SELECT COUNT(*) FILTER (WHERE airport_name LIKE '%ovo') AS like_ovo,
       COUNT(*) FILTER (WHERE city ILIKE 'novo%')       AS ilike_novo
FROM airports;

ILIKE works in PostgreSQL and DuckDB but not in MySQL or SQL Server, where case sensitivity depends on the column’s collation.

SUBSTRING, LEFT, RIGHT and SPLIT_PART: cutting text

substring(text, start, length) returns part of a string. Positions start at 1, and if you leave out the length you get everything to the end. Flight numbers are the airline code PG plus four digits:

SELECT flight_no,
       substring(flight_no, 3) AS num,
       CAST(substring(flight_no, 3) AS INTEGER) AS num_int,
       right(flight_no, 4) AS r
FROM flights
WHERE flight_id IN (1, 2);

num is still text ('0405'); cast it if you need to sort or compare numerically, otherwise '1000' sorts before '999'. LEFT(text, n) and RIGHT(text, n) are shortcuts for the first or last n characters. PostgreSQL also accepts the standard form substring(text FROM 3 FOR 4); MySQL and SQL Server use SUBSTRING(text, 3, 4) too, while Oracle calls it SUBSTR.

When the text has a delimiter, split_part(text, delimiter, n) is easier than counting positions. Splitting the passenger name into first and last name, and the same thing done with POSITION:

SELECT passenger_name,
       split_part(passenger_name, ' ', 1) AS first_name,
       split_part(passenger_name, ' ', 2) AS last_name,
       position(' ' IN passenger_name) AS space_at,
       substring(passenger_name FROM 1 FOR position(' ' IN passenger_name) - 1) AS first2,
       left(passenger_name, 1) AS initial
FROM tickets
LIMIT 3;

position(' ' IN x) and strpos(x, ' ') are the same thing in PostgreSQL and DuckDB; they return 0 when the substring is missing. split_part exists in PostgreSQL, DuckDB and Snowflake; MySQL has SUBSTRING_INDEX instead, and SQL Server has neither.

Once a piece is extracted, you can group by it. The most common first names on tickets:

SELECT split_part(passenger_name, ' ', 1) AS first_name,
       COUNT(*) AS tickets
FROM tickets
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;

REPLACE and REGEXP_REPLACE

replace(text, from, to) swaps every occurrence of a substring:

SELECT model,
       replace(model, 'Sukhoi ', '') AS short,
       replace(model, '-', ' ') AS no_dash
FROM aircrafts
WHERE model LIKE 'S%' OR model LIKE 'Bombardier%';

If the substring is not there, the value comes back unchanged. Replacing with '' deletes.

When the thing to replace is a pattern rather than fixed text, use a regular expression. Seat numbers combine a row and a letter (10A); split them by removing digits or letters:

SELECT seat_no,
       regexp_replace(seat_no, '[0-9]', '', 'g') AS letter,
       CAST(regexp_replace(seat_no, '[A-Z]', '', 'g') AS INTEGER) AS row_no
FROM seats
WHERE aircraft_code = '319'
ORDER BY row_no, letter
LIMIT 4;

The 'g' flag means “global”: replace every match, not just the first. Without it, regexp_replace('12A', '[0-9]', '') gives 2A. This is the same in PostgreSQL and DuckDB. To pull a match out rather than replace it, PostgreSQL uses substring(text FROM 'pattern') or regexp_substr (version 15+), and DuckDB uses regexp_extract(text, 'pattern').

TRIM, LTRIM, RTRIM and LPAD

trim removes leading and trailing spaces, the most common reason two values that look equal do not match in a join:

SELECT '|' || trim('  SVO  ')  || '|' AS t,
       '|' || ltrim('  SVO  ') || '|' AS l,
       '|' || rtrim('  SVO  ') || '|' AS r,
       trim(BOTH '0' FROM '0405') AS z,
       ltrim('0405', '0') AS lz;

The pipes make the spaces visible. trim can also strip other characters, such as leading zeros. length(' SVO ') is 6 while length(trim(' SVO ')) is 3, which is a quick way to find dirty values: WHERE length(code) <> length(trim(code)).

lpad(text, length, fill) does the reverse and pads on the left. The next flight number after PG0405, keeping four digits:

SELECT flight_no,
       lpad(CAST(CAST(substring(flight_no, 3) AS INTEGER) + 1 AS VARCHAR), 4, '0') AS next_no
FROM flights
WHERE flight_id = 1;

Text inside JSON: ->>

Contact details are a JSON column. The ->> operator returns a field as text, and from there all the string functions apply:

SELECT ticket_no,
       contact_data ->> 'phone' AS phone,
       contact_data ->> 'email' AS email,
       right(contact_data ->> 'phone', 4) AS last4
FROM tickets
LIMIT 3;

A missing key returns NULL, not an empty string. 199,083 of the 366,733 tickets have an email, and you would count them with COUNT(*) FILTER (WHERE contact_data ->> 'email' IS NOT NULL). ->> works on json and jsonb in PostgreSQL and on JSON in DuckDB.

STRING_AGG: many rows into one string

The opposite of splitting: collapse a group’s values into one comma-separated cell. Cities with more than one airport:

SELECT city,
       string_agg(airport_code, ', ' ORDER BY airport_code) AS airports,
       COUNT(*) AS n
FROM airports
GROUP BY city
HAVING COUNT(*) > 1
ORDER BY city;

string_agg works in PostgreSQL, DuckDB and SQL Server (2017+). MySQL calls it GROUP_CONCAT; Oracle and Snowflake call it LISTAGG. HAVING is explained in GROUP BY vs HAVING.

Quick reference: PostgreSQL vs DuckDB vs others

Common mistakes

• Using || with a nullable column. One NULL empties the whole label. Use CONCAT, concat_ws or COALESCE.
• Comparing without normalising. 'SVO ' with a trailing space and 'svo' in lower case will never equal 'SVO'. trim and lower/upper both sides before joining text keys.
• Sorting numbers stored as text. Cast the extracted digits to INTEGER before ORDER BY or MAX.
• Off-by-one positions. SQL strings start at position 1, not 0, and position returns 0 when nothing is found, so substring(x, 1, position(...) - 1) returns an empty string for values without the delimiter.
• Forgetting the 'g' flag in regexp_replace, so only the first match changes.
• Double quotes for text. In PostgreSQL "SVO" is a column name; text goes in single quotes.

Dates have their own set of functions, covered in SQL date functions. For more practice with real interview-style tasks, see SQL practice problems.

FAQ

How do I concatenate strings in SQL?

In PostgreSQL and DuckDB use first || ' ' || last or CONCAT(first, ' ', last). CONCAT ignores NULL values, || returns NULL if any part is NULL. In MySQL use CONCAT; in SQL Server, + or CONCAT.

What is the difference between SUBSTRING and SPLIT_PART?

SUBSTRING cuts by position (characters 3 to 6). SPLIT_PART cuts by delimiter (the second word after splitting on spaces). Use SPLIT_PART when the pieces vary in length but are separated by a known character.

How do I remove spaces from a string in SQL?

trim(x) removes spaces at both ends, ltrim and rtrim only on one side. To remove every space, including those in the middle, use replace(x, ' ', '').

How do I make a SQL comparison case-insensitive?

Compare lowered values, lower(city) = 'moscow', or use ILIKE in PostgreSQL and DuckDB. In MySQL and SQL Server, comparisons are often case-insensitive already, depending on collation.

Does PostgreSQL have a LEN function?

No. Use length(x) or char_length(x). LEN is SQL Server; note that SQL Server’s LEN ignores trailing spaces while PostgreSQL’s length on text counts them.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文