Combining First Name and Last Name into a Full Name Column

Your customer table has first_name and last_name in separate columns. Your mailing list provider wants a single "Full Name" field. Your report template expects one name column, not two. And if you just smash them together with a space, you'll end up with names like "Jane " (no last name) or " Smith" (no first name) scattered through your export.

This is one of the most common column operations in data preparation. Here's how to do it properly in ExploreMyData, handling NULLs along the way.

The simple case: both names exist

If every row has both a first and last name, combining them is straightforward. Click the green + in the Pipeline panel and select Combine Columns from the Columns group. The panel has a single control: a chip input labelled "Define values or columns to combine", with the placeholder "Type text or select a column...". There is no separator field and no multi-select column list. You build the output chip by chip, in order:

1. Pick first_name from the column list. It drops in as a column chip.
2. Type a single space and press Enter. That becomes a literal text chip, and it is the only thing standing between the two names.
3. Pick last_name for the third chip.
4. Under Apply results into, choose New Column and name it full_name. Leave the name blank and you get one called combined.

The generated SQL looks like this:

CONCAT(COALESCE(CAST("first_name" AS VARCHAR), ''), ' ', COALESCE(CAST("last_name" AS VARCHAR), '')) AS "full_name"

For a table like this:

first_name	last_name	full_name
Jane	Doe	Jane Doe
Carlos	Rivera	Carlos Rivera
Priya	Sharma	Priya Sharma

This works perfectly when your data is clean. But customer data is rarely clean.

The real case: NULL first or last names

Open any real customer export and you'll find rows where first_name is NULL (the person signed up with just a last name), or last_name is NULL (a single-name entry, or just incomplete data). Combine Columns never hands a NULL back to you: every column chip arrives already wrapped in COALESCE(CAST(… AS VARCHAR), ''), so a missing name contributes an empty string. What you get instead is a stray space.

A row with no first name comes out as " Smith", leading space and all. A row with no last name comes out as "Jane ". Neither belongs on a mailing label.

Trimming the leftover space

The COALESCE comes for free, so the only thing left to fix is the space. That's a second step:

1. Use Combine Columns with the three chips above.
2. Apply Text Transform from the Transform group on the new full_name column, with the "trim" option. Text Transform updates the column in place, so you don't end up with a second name column.

Each step is its own view in the pipeline, so you see them one after the other rather than as one big expression:

CONCAT(COALESCE(CAST("first_name" AS VARCHAR), ''), ' ', COALESCE(CAST("last_name" AS VARCHAR), '')) AS "full_name"

TRIM("full_name") AS "full_name"

Now your results are clean:

When both names are missing

Notice the last row in the table above. Both names are NULL, so full_name comes out as an empty string, and you probably want it to read "Unknown".

The obvious-looking answer is Fill Missing. It will not work, and it's worth knowing why before you spend ten minutes wondering. Fill Missing only fills NULL, through a COALESCE. Combine Columns can't produce a NULL, because it already COALESCEs every chip. Two missing names give you '', Fill Missing sees a perfectly good value, and the step changes nothing.

Use Update Values from the Transform group instead. Set Apply results into to Existing Column, full_name, put Unknown in the Value box, then add a condition: full_name is Empty. That one operator covers the NULL case and the empty-string case together:

CASE WHEN ("full_name" IS NULL OR "full_name" = '') THEN 'Unknown' ELSE "full_name" END AS "full_name"

Find & Replace is the right tool for a different version of this problem: blanks that arrive as a placeholder rather than as nothing at all. Pick the column, tick Entire cell, and swap an exact value such as N/A for Unknown. It needs a value in the Find box, so it can't catch a genuinely empty cell on its own.

Adding more parts to the name

The chip input isn't limited to two columns and a space. You can build any pattern. Need "Last, First" format for a directory? Three chips again, in this order:

1. last_name column chip
2. a literal chip holding a comma and a space
3. first_name column chip

Or if you have a middle_initial column, add it between the first and last name chips with a space chip on each side.

The pipeline keeps both versions

Combine Columns creates a new column, it doesn't replace the originals. Your first_name and last_name columns stay intact. If you want to drop them after combining, add a Delete Columns step below the combine. Every step stays visible in the pipeline and any of them can be deleted, though they can't be dragged into a new order, so put the combine before the delete.

Try combining columns in ExploreMyData →