How to split a column in a CSV

A full name that should be first and last name. An email where you only want the domain. A cell holding a whole list. Synth loads your CSV into a SQLite database in your browser, so you can split any column with a short query and download the result as a new CSV. Nothing is installed, and the file never leaves your computer.

The example file

Say you drop a contacts.csv like this into Synth. It becomes a table called contacts:

id | full_name      | email            | tags
1  | Ana Lopez      | ana@acme.com     | sql;excel
2  | Ben Okafor     | ben@globex.io    | csv
3  | Mary Ann Smith | mary@acme.com    | sql;csv;python
4  | Cher           | cher@example.com |

Every split below uses two SQLite functions. instr(text, 'x') finds the position of a separator, and substr(text, start, length) takes the part you want.

Split a full name into first and last name

Take everything before the first space as the first name and everything after it as the last name. The CASE keeps single names like Cher whole instead of losing them:

SELECT full_name,
       CASE WHEN instr(full_name, ' ') = 0 THEN full_name
            ELSE substr(full_name, 1, instr(full_name, ' ') - 1) END AS first_name,
       CASE WHEN instr(full_name, ' ') = 0 THEN ''
            ELSE substr(full_name, instr(full_name, ' ') + 1) END AS last_name
FROM contacts;

full_name      | first_name | last_name
Ana Lopez      | Ana        | Lopez
Ben Okafor     | Ben        | Okafor
Mary Ann Smith | Mary       | Ann Smith
Cher           | Cher       |

Middle names end up in last_name, as with Mary Ann Smith. To find those rows, add WHERE full_name LIKE '% % %' and decide how to handle them.

Split an email into username and domain

SELECT email,
       substr(email, 1, instr(email, '@') - 1) AS username,
       substr(email, instr(email, '@') + 1)    AS domain
FROM contacts;

Once the domain is its own column you can group by it, for example to see which companies most of your contacts come from:

SELECT substr(email, instr(email, '@') + 1) AS domain,
       COUNT(*) AS contacts
FROM contacts
GROUP BY domain
ORDER BY contacts DESC;

Here acme.com comes first with 2 contacts.

Split a list in one cell into separate rows

When a cell holds several values, like sql;csv;python, a recursive query can peel them off one at a time so each value gets its own row:

WITH RECURSIVE split(id, tag, rest) AS (
  SELECT id, '', tags || ';' FROM contacts WHERE tags <> ''
  UNION ALL
  SELECT id,
         substr(rest, 1, instr(rest, ';') - 1),
         substr(rest, instr(rest, ';') + 1)
  FROM split
  WHERE rest <> ''
)
SELECT id, tag
FROM split
WHERE tag <> ''
ORDER BY id;

id | tag
1  | sql
1  | excel
2  | csv
3  | sql
3  | csv
3  | python

To use a comma or another separator, change the three ';' characters. Replace the last SELECT with SELECT tag, COUNT(*) FROM split WHERE tag <> '' GROUP BY tag to count how often each value appears.

Save the split columns as a new CSV

Click Download CSV above any query result. Your original file is not changed.

Don't want to write SQL?

Sign in and ask the AI assistant something like "split full_name into first and last name." It writes the query from your column names, never your data.

Frequently asked questions