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| host_id | first_name | last_name |
|---|---|---|
| 1 | Maria | Lopez |
| 2 | Ken | NULL |
| 3 | Amara | Okafor |
| 4 | Lin | Zhao |
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| host_id | first_name | last_name | listing_count |
|---|---|---|---|
| 1 | Maria | Lopez | 3 |
| 2 | Ken | NULL | 1 |
| 3 | Amara | Okafor | 12 |
| 4 | Lin | Zhao | 0 |
| host_count | hosts_with_full_name |
|---|---|
| 4 | 3 |
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.
| host_id | first_name | last_name | listing_count |
|---|---|---|---|
| 1 | Maria | Lopez | 3 |
| 2 | Ken | NULL | 1 |
| 3 | Amara | Okafor | 12 |
| 4 | Lin | Zhao | 0 |
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| host_id | full_name |
|---|---|
| 1 | Maria Lopez |
| 2 | NULL |
| 3 | Amara Okafor |
| 4 | Lin 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| host_id | full_name |
|---|---|
| 1 | Maria Lopez |
| 2 | NULL |
| 3 | Amara Okafor |
| 4 | Lin 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| host_id | summary |
|---|---|
| 1 | Maria has 3 listings |
| 2 | Ken has 1 listings |
| 3 | Amara has 12 listings |
| 4 | Lin 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?
- 'Ken ', the known pieces joined with the NULL skipped over
- 'Ken NULL', with the missing value written out as text
- An error, because CONCAT cannot accept a NULL argument
- NULL, because one unknown piece makes the whole string unknown
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.
| expression | result 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.