String Split in SQLite

If you've ever imported data into a SQLite database and found a column like location that stores values in the format "City, State" — for example, "Los Angeles, CA" — you might wonder how to split it into two separate columns: city and state.

Unlike other SQL dialects, SQLite doesn’t support a SPLIT() function. But we can still split the string using INSTR() and SUBSTR() effectively.

Splitting "City, State" in SQLite

Assuming you have a table like follows.

CREATE TABLE places (
    id INTEGER PRIMARY KEY,
    location TEXT
);

INSERT INTO places (location) VALUES
('Los Angeles, CA'),
('Chicago, IL'),
('Houston, TX'),
('Phoenix, AZ'),
('Philadelphia, PA');

Our goal is to split the location column into:

• city: The part before the comma
• state: The part after the comma (and space)

Query Using instr() and substr()

SELECT
    location,
    TRIM(SUBSTR(location, 1, INSTR(location, ',') - 1)) AS city,
    TRIM(SUBSTR(location, INSTR(location, ',') + 1)) AS state
FROM places;

Conclusion

Even though SQLite lacks advanced string functions like SPLIT(), we can still achieve flexible string manipulation using INSTR() and SUBSTR(). This makes your database more efficient and your queries more powerful.

If you're dealing with any delimited data, this approach is your Swiss Army knife. Happy querying!