Split address with multiple delimeter to multiple column in SQL

February 23, 2022

1808 FOX CHASE DR, GOODLETTSVILLE, TN i am trying to split it in 3 column based on , comma.

I have used select substring_index(OwnerAddress,',',1),substring_index(OwnerAddress,',',-1) from housingdata; it will give me only 2 values however i am lookking for 3 values in 3 different column.

i am using Mysql Workbench. can anyone guide on how to split string with multiple delemter in sql.

>Solution :

mysql> set @a = '1808  FOX CHASE DR, GOODLETTSVILLE, TN';

mysql> select trim(substring_index(@a, ',', 1)) as address,
  trim(substring_index(substring_index(@a, ',', 2), ',', -1)) as city,
  trim(substring_index(@a, ',', -1)) as state;
+--------------------+----------------+-------+
| address            | city           | state |
+--------------------+----------------+-------+
| 1808  FOX CHASE DR | GOODLETTSVILLE | TN    |
+--------------------+----------------+-------+

On the other hand, if you want these three data elements to be separate, you should store them in three separate columns. This sort of query is bound to be trouble if someone has an address that contains a comma.

See https://linguaholic.com/linguablog/commas-in-adresses/