0Pricing
SQL Academy · Lesson

Concatenation: || and CONCAT

Concatenate strings with the || operator and CONCAT(), and understand how each handles NULL inputs.

Concatenation: || and CONCAT is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Standard Concatenation: ||

The SQL-standard operator for joining strings is ||:

SELECT first_name || ' ' || last_name AS full_name FROM users;
SELECT 'Hello, ' || name || '!' FROM users;

|| with NULL = NULL

If any operand is NULL, the whole expression becomes NULL. Be explicit:

SELECT first_name || ' ' || NULL;     -- NULL

-- Defend with COALESCE:
SELECT first_name || ' ' || COALESCE(last_name, '') FROM users;

CONCAT: NULL-Safe Alternative

CONCAT treats NULL as empty string:

SELECT CONCAT(first_name, ' ', last_name) FROM users;
SELECT CONCAT(first_name, ' ', NULL);     -- 'first_name ' (no NULL)

CONCAT_WS: With Separator

The most readable for "join with separator":

SELECT CONCAT_WS(' ', title, first_name, middle_initial, last_name) AS pretty_name
FROM users;
-- ' ' between non-NULL fields only

Mixing Strings and Numbers

You can cast or rely on implicit conversion (PostgreSQL is strict — explicit casts are safer):

SELECT 'Order #' || id::TEXT  FROM orders;
SELECT CONCAT('Order #', id) FROM orders;     -- CONCAT auto-casts

Building Display Strings

Compose readable labels:

SELECT id,
       email,
       CONCAT_WS(', ', city, region, country) AS location
FROM users;

Concatenating Aggregates with STRING_AGG

To join values from many rows, use STRING_AGG (or array_agg):

SELECT user_id,
       STRING_AGG(name, ', ' ORDER BY name) AS tag_names
FROM user_tags
GROUP BY user_id;

FORMAT for Templated Strings

PostgreSQL's format() is printf-style and NULL-safe:

SELECT format('User %s (id=%s) joined on %s',
              full_name, id, created_at::DATE)
FROM users;

Empty String vs NULL

Decide for each column: is empty string a valid value? If not, treat '' on input as NULL:

INSERT INTO contacts (phone) VALUES (NULLIF(:phone, ''));

Multi-Line String Literals

PostgreSQL supports dollar-quoting for multi-line strings without escaping:

SELECT $$Hello,
'World' — with quotes!$$;

SELECT $custom_tag$ ... $custom_tag$;

Concatenation Performance

Concatenation is fast — millions per second. The cost is usually the I/O of the rows, not the string ops.

Recap

Use || for standard concat, CONCAT/CONCAT_WS to ignore NULLs, FORMAT for templates, STRING_AGG to concatenate many rows.

Quick Check

If last_name is NULL, what does first_name || ' ' || last_name return?

Frequently asked questions

Is the “Concatenation: || and CONCAT” lesson free?

Yes — the full text of “Concatenation: || and CONCAT” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Concatenation: || and CONCAT”?

Concatenate strings with the || operator and CONCAT(), and understand how each handles NULL inputs. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Concatenation: || and CONCAT” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Concatenation: || and CONCAT
  2. UPPER, LOWER, SUBSTRING, REPLACE
  3. TRIM, LPAD, RPAD
  4. Full-Text Search Introduction (tsvector, to_tsquery)
← Back to SQL Academy