CONCAT()

Concepts covered: String Joining

CONCAT joins pieces of text in order

CONCAT takes 2 or more text arguments and returns 1 string made of them, in the order you listed them. It inserts nothing between the pieces: CONCAT('Maria', 'Lopez') returns 'MariaLopez'. A space is a piece of text like any other, so you pass it as its own argument, ' ', at the position where you want it to appear. The hosts table stores first and last names in separate columns, and a report wants one full name per host.

SELECT
  host_id,
  CONCAT(first_name, ' ', last_name) AS full_name
FROM hosts
hosts
host_idfirst_namelast_name
1MariaLopez
2KenNULL
3AmaraOkafor
4LinZhao

Three hosts get a full name. Ken gets NULL, and the next heading explains why. The || operator does the same work 2 pieces at a time: first_name || ' ' || last_name reads left to right and produces an identical result. Choose whichever reads better in the query you are writing; the database treats them as one operation.

One NULL argument makes the whole result NULL

Ken has no last name on file, so last_name is NULL. CONCAT('Ken', ' ', NULL) returns NULL. The partial string 'Ken ' is never produced, and the word NULL is never written into the text. CONCAT follows the rule most functions follow: when any input is NULL, the output is NULL, because a string built from an unknown piece is itself unknown. The consequence shows up in aggregates. COUNT(*) counts Ken, COUNT of the full name skips him, and a report that lists full names shows a blank where Ken should be.

SELECT
  COUNT(*) AS host_count,
  COUNT(CONCAT(first_name, ' ', last_name)) AS hosts_with_full_name
FROM hosts
hosts
host_idfirst_namelast_namelisting_count
1MariaLopez3
2KenNULL1
3AmaraOkafor12
4LinZhao0
Result
host_counthosts_with_full_name
43

Supplying a default for the missing piece, so Ken shows as 'Ken' rather than NULL, uses a function from a later lesson. For now, IS NULL on the result finds the rows that need attention.

Numbers must become text before CONCAT accepts them

Every argument to CONCAT must be VARCHAR. Pass an INTEGER column straight in and the query fails with a type error before it reads a row, because the database will not guess whether 12 should become '12' or something else. CAST(listing_count AS VARCHAR) turns 12 into the 2-character string '12', and CONCAT accepts it. The 3 versions below build a label 3 ways over the same hosts table; the third one needs the CAST because listing_count is a number.

hosts
host_idfirst_namelast_namelisting_count
1MariaLopez3
2KenNULL1
3AmaraOkafor12
4LinZhao0

CONCAT

CONCAT with 3 arguments: the first name, a space, the last name.

SELECT
  host_id,
  CONCAT(first_name, ' ', last_name) AS full_name
FROM hosts
Result
host_idfull_name
1Maria Lopez
2NULL
3Amara Okafor
4Lin Zhao

Pipes

The || operator joining the same 3 pieces left to right, with the same result and the same NULL for Ken.

SELECT
  host_id,
  first_name || ' ' || last_name AS full_name
FROM hosts
Result
host_idfull_name
1Maria Lopez
2NULL
3Amara Okafor
4Lin Zhao

With CAST

A sentence per host, where the integer listing_count becomes text through CAST before CONCAT sees it.

SELECT
  host_id,
  CONCAT(first_name, ' has ', CAST(listing_count AS VARCHAR), ' listings') AS summary
FROM hosts
Result
host_idsummary
1Maria has 3 listings
2Ken has 1 listings
3Amara has 12 listings
4Lin has 0 listings

The third tab shows 'Ken has 1 listings' with a plural that reads badly, and 'Lin has 0 listings' for a host with nothing published. Both strings are correct output: CONCAT assembles what it is given and applies no grammar. Deciding when to write 'listing' instead of 'listings' needs a conditional expression, which arrives in a later lesson.

What does CONCAT('Ken', ' ', NULL) return?

Building a label piece by piece

A label that puts a first name in front of a number takes 3 arguments, and each one does a job. Leave out the separator and the name runs into the number. Leave out the CAST and the query fails before it reads a row. The table shows the 3 attempts on a single row, where listing_count is 12.

expressionresult on Amara's row
CONCAT(first_name, CAST(listing_count AS VARCHAR))'Amara12'
CONCAT(first_name, ' ', listing_count)type error: listing_count is an INTEGER
CONCAT(first_name, ' ', CAST(listing_count AS VARCHAR))'Amara 12'

About this lesson

This section is part of the String Functions: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.