Ravindra BagaleCourses & study guides Track your progress

10. Text Functions Analysts Use Daily

10.2 How to Glue Text with || and concat() (Names and Labels)

By Last updated:

Why. Reports often need one readable label, such as a hub code with its city. Gluing text in SQL saves you from doing it by hand in Excel.

What. The || operator joins two pieces of text into one. If any piece is NULL, the whole result becomes NULL. In SQLite 3.44 and later, concat() also joins text but simply skips NULL pieces. Other databases handle NULL in their own way, so check before you move a query.

How. Build a hub label with ||. Then compare || and concat() on an agent whose name is missing.

Steps

  1. Run the first example to build labels like PNQ-01 - Pune.
  2. Run the second example for agents 1 and 6.
  3. See that the || column is NULL for agent 6, while concat() still returns ' (1)'.
  4. Decide what your report should show when a name is missing, for example by using coalesce(raw_name, 'Unknown').

Examples

Example 1 · dataset: swiftship_logistics

SELECT hub_code || ' - ' || city AS hub_label
FROM hubs
ORDER BY hub_id;

Output

hub_label
PNQ-01 - Pune
BOM-02 - Mumbai
DXB-01 - Dubai
LHR-03 - London
SIN-01 - Singapore

Meaning. Each hub now has one clear label with its code and city, ready for a report heading.

Example 2 · dataset: swiftship_logistics

SELECT agent_id,
       raw_name || ' (' || hub_id || ')'      AS label_pipes,
       concat(raw_name, ' (', hub_id, ')')    AS label_concat
FROM agents
WHERE agent_id IN (1, 6);

Output

agent_idlabel_pipeslabel_concat
1 asha patil (1) asha patil (1)
6NULL (1)

Meaning. For agent 6 the name is NULL, so the || label is NULL too. concat() skips the missing name and returns only ' (1)'. Neither result is a good label, so handle the missing name on purpose.

Ravindra Bagale's Tip

When a label suddenly shows NULL, look for one NULL piece inside the || chain. Wrap that column in coalesce() with a clear word like Unknown.