Splitting strings in SQLite

Question

How do I split my address columns into three new columns separated by its commas?

1808  FOX CHASE DR, GOODLETTSVILLE, TN

Divided into:

• 1808 FOX CHASE DR
• GOODLETTSVILLE
• TN

I've tried:

SELECT
    SUBSTRING(OwnerAddress, 1, INSTR(OwnerAddress, ',')-1) as col1,
    SUBSTRING(OwnerAddress, INSTR(OwnerAddress, ',')+1) as col2  
FROM housing_data;

I cannot create the third column with the state abbreviation.

Answer 1

It's generally much cleaner to do any sort of string processing in the target library. That said, if you must, you can make liberal use of SUBSTRING and INSTR to find each comma and split the strings:

SELECT 
    SUBSTRING(OwnerAddress, 1, INSTR(OwnerAddress, ',') - 1) as col1,
    SUBSTRING(SUBSTRING(OwnerAddress, INSTR(OwnerAddress, ',') + 1), 1, INSTR(SUBSTRING(OwnerAddress, INSTR(OwnerAddress, ',') + 1), ',') - 1) as col2,
    SUBSTRING(SUBSTRING(OwnerAddress, INSTR(OwnerAddress, ',') + 1), INSTR(SUBSTRING(OwnerAddress, INSTR(OwnerAddress, ',') + 1), ',') + 1) as col3
FROM housing_data;