Split address by delimer and create new column in MySQL Workbench-CodePudding

I want to split address based on delimeter (,)

the code i have written select substring_index(Propertyaddress,',') from housingdata; however it shows an error saying incorrect parameter call to native function i am using mysql workbench.

CodePudding user response：

substring_index needs a third parameter.

Try this to get the part before and the part after the comma ,:

select substring_index(Propertyaddress,',', 1),
select substring_index(Propertyaddress,',', -1) 
from housingdata;

substring_index returns a substring from a string before the specified number of occurrences of the delimiter. With 1 as the third parameter, it gets the part of the string before the first encountered comma. With -1 it returns the substring from the right of the last comma.