Text Concatenation and Formatting

Text Concatenation and Formatting

In this lesson, you will learn how to stitch separate pieces of text together into a single, clean, and human-readable string.

So far, we have focused on pulling data out of columns exactly as they exist in the database. However, raw data is often fragmented. For example, you might have a first_name column and a last_name column. If you want to display the "Full Name" in a report, you need to combine those separate fields into one.

The Concatenation Process

Concatenation is the act of linking two or more items together. In SQL, we achieve this by using the pipe operator (||) or a specific function like CONCAT(). When we join these fields, we often need to inject extra formatting—like a space, a comma, or a hyphen—to make the final string look natural.

Consider a table of employees where you want to list them as "Last Name, First Name":

SELECT last_name || ', ' || first_name AS display_name
FROM employees;

Breaking Down the Syntax

In the example above, the || operator acts as the glue. We tell the database to take the value from the last_name column, add a literal string containing a comma and a space (', '), and then attach the first_name column to the end.

• The Columns: last_name and first_name provide the dynamic data.
• The Literal: ', ' is a hard-coded string we insert manually to format the data correctly for a human reader.
• The Alias: We use AS display_name to give our new, combined column a clean label in the results.

This technique is incredibly useful for generating labels for user interfaces, constructing addresses from individual parts, or creating descriptive status messages. By transforming fragmented data into a cohesive string during the extraction phase, you save yourself the trouble of manually formatting the data later in your application code.

Key Takeaway: Use concatenation operators or functions to combine multiple data fields with custom text literals to create formatted, human-readable strings directly in your query output.