Splitting strings in SQLite

Does anyone know how to split my address columns. I would like to divide it into three new columns, seperated by it’s commas. For example 1808 FOX CHASE DR, GOODLETTSVILLE, TN is divided into

• 1808 FOX CHASE DR
• GOODLETTSVILLE
• TN

So far I’ve tried

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

Although I cannot create the third column with the state abbreviation.

Thanks for any input

Answer

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;