BBampaa

Lessons › SQL › Chapter 8: Text, dates and safe maths

SQL lesson

Joining text with CONCAT and ||

Sometimes the text you need is split across columns, like a first name and a last name. CONCAT joins pieces of text into one, in the order you list them. You can add your own text too, in quotes, like a space or a dash. || does the same job.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Put text together

SELECT customer_id, CONCAT(first_name, ' ', last_name) AS full_name
FROM customers

Result:

customer_idfull_name
C101Ada Obi
C102John Mensah
C103Fatima Bello
C104Kwame Asante
C105Ngozi Eze

Step by step

  1. The pieces, in order. The space in quotes is a piece too.
    SELECT customer_id, CONCAT(first_name, ' ', last_name) AS full_name FROM customers
  2. CONCAT joins the pieces into one text, in the order you list them.
    SELECT customer_id, CONCAT(first_name, ' ', last_name) AS full_name FROM customers
  3. AS names the new column.
    SELECT customer_id, CONCAT(first_name, ' ', last_name) AS full_name FROM customers
  4. If a piece is empty, CONCAT leaves it out, but || makes the whole result empty.
    SELECT customer_id, CONCAT(first_name, ' ', last_name) AS full_name FROM customers

Practice

Try each one in your head first, then open the answer.

1. Using customers, show customer_id and each customer's full name: first name, a space, then last name. Name it full_name.
SELECT customer_id, CONCAT(first_name, ' ', last_name) AS full_name FROM customers
2. Using customers, show customer_id and a label like Ada Obi (Lagos): the full name, then the city in brackets. Name it label.
SELECT customer_id, CONCAT(first_name, ' ', last_name, ' (', city, ')') AS label FROM customers
3. Using customers, show customer_id and the full name again, this time with ||. Name it full_name.
SELECT customer_id, first_name || ' ' || last_name AS full_name FROM customers
4. Using products, show product_id and a label like NovaTech - Electronics: the brand, a dash with a space each side, then the category. Name it label.
SELECT product_id, CONCAT(brand, ' - ', category) AS label FROM products
5. Using orders, show order_id and a label like Order O2001. Name it order_label.
SELECT order_id, CONCAT('Order ', order_id) AS order_label FROM orders

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed