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. Open the toolbar, select Combine Columns from the Columns group, and build a token sequence:

1. Add the first_name column as the first token.
2. Add a literal string token: a single space " ".
3. Add the last_name column as the third token.
4. Name the new column full_name.

The generated SQL looks like this:

CONCAT(first_name, ' ', last_name) 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). When you concatenate a NULL with a string in SQL, the behavior depends on the function. DuckDB's CONCAT() treats NULLs as empty strings, so you won't get a NULL result, but you will get a leading or trailing space.

CONCAT(NULL, ' ', 'Smith') produces " Smith" with a leading space. Not what you want in a mailing label.

Handling NULLs with COALESCE

The fix is to wrap each name column with COALESCE, falling back to an empty string, and then trim the result. In ExploreMyData, you can handle this in two steps:

1. Use Combine Columns with the same token setup (COLUMN + STRING + COLUMN). The tool automatically uses CONCAT() which handles NULLs as empty strings.
2. Apply Text Transform on the new full_name column with the "trim" option to strip any leading or trailing spaces.

Under the hood, the pipeline generates SQL like:

TRIM(CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, ''))) AS full_name

Now your results are clean:

first_name	last_name	full_name
Jane	Doe	Jane Doe
NULL	Smith	Smith
Carlos	NULL	Carlos
NULL	NULL	(empty)

When both names are NULL

Notice the last row in the table above. If both first and last name are NULL, you get an empty string. Depending on your use case, you might want that to say "Unknown" instead. Add a Fill Missing step on the full_name column with a literal value of "Unknown". Or, if empty strings are the issue rather than NULLs, use Find & Replace to replace the exact empty match with your preferred fallback.

Adding more parts to the name

The token-based Combine Columns operation isn't limited to two columns and a space. You can build any pattern. Need "Last, First" format for a directory? Set up the tokens as:

1. last_name column
2. ", " literal string
3. first_name column

Or if you have a middle_initial column, add it between the first and last name tokens with spaces 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 Drop Columns step to remove the originals. Every step is visible in the pipeline, and you can delete or reorder any of them.

Try combining columns in ExploreMyData →