Splitting strings in SQLite-CodePudding

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

CodePudding user response：

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;