I'm trying to clean this data set and the home#dest column is giving me some issues. I would like to turn the one column into 4. they would be Homecity, Homestate/country, Destcity, and Deststate/country. I've tried many different combinations of functions, butI'm trying to clean this data set and the home#dest column is giving me some issues. I would like to turn the one column into 4. they would be Homecity, Homestate/country, Destcity, and Deststate/country. I've tried many different combinations of functions, but I'm new to data analytics and I'm not sure what to do. The problem is the column's data has no standard format. Some columns have all the info I'd need for my new columns will some just have one, or none. I don't mind having no info if it's not available. Charindex doesn't seem to work because not every row has a comma and also trying to find the first delimiter could wither be a comma, dash, or slash Any assistiance would be appreciated. I'm using MSSQL.
I tried this to get me started.
Select
SUBSTRING(home#dest, 1, CHARINDEX(',' , home#dest)) AS Homecity,
PARSENAME(REPLACE ( home#dest , '/' , '.' ), 2) AS HomeCountry,
PARSENAME(REPLACE ( home#dest , '/' , '.' ), 1) AS Destination
From PortfolioProjects..titanic
Where home#dest is not NULL
