How does the SQL version of Text to Columns work?

SPLIT_PART(location, ',', 1) returns the first piece before the comma, and 2 returns the second. TRIM removes the space that usually follows the comma. Each output column is one of the new columns Excel would create.

Why can SQL give a different result from Text to Columns?

Text to Columns overwrites the original column in the sheet; the SQL leaves the source column unchanged and returns new ones.

Text to Columns in SQL

Text to Columns splits one text column into several using a delimiter such as a comma; in SQL you use SPLIT_PART or a similar function.

The Excel version

Data → Text to Columns → Delimited → Comma, or =TEXTSPLIT(A2, ",")

The SQL equivalent

This example assumes: contacts(id bigint, full_name text, location text). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT id,
       TRIM(SPLIT_PART(location, ',', 1)) AS city,
       TRIM(SPLIT_PART(location, ',', 2)) AS country
FROM contacts
ORDER BY id;

How it works

SPLIT_PART(location, ',', 1) returns the first piece before the comma, and 2 returns the second.
TRIM removes the space that usually follows the comma.
Each output column is one of the new columns Excel would create.

Where the results differ from Excel

Text to Columns overwrites the original column in the sheet; the SQL leaves the source column unchanged and returns new ones.
If a value has fewer pieces than you ask for, SPLIT_PART returns an empty string rather than leaving the cell blank.
SPLIT_PART is PostgreSQL; MySQL uses SUBSTRING_INDEX, and BigQuery uses SPLIT with an offset.

Skip the query: ask in plain English

If you would rather not write SQL, type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the result matches how your spreadsheet defines the number.

Split the location column into city and country.
How many contacts are in each country?