Whats the best way to split a comma list column into different columns? I am looking for a simple algorithm, heard it is best to avoid scalar value functions, as they might be slow. Searching into many methods, substring, find, splitstring; we need to run this algorithm across millions of addresses, so looking for best, optimal answer with good coding practice.
create table dbo.AddressTest (AddressLine varchar(255)) insert into dbo.AddressTest (AddressLine) values ('123 Maple Street, Austin, Texas, 78653, 555-234-4589') -- may not require substring, just looking for good way select substring as AddressStreet -- Expected: 123 Maple Street substring(...) as City, -- Expected: Austin substring(...) as State, -- Expected: Texas substring(...) as ZipCode -- Expected: 78653 substring(...) as PhoneNumber -- Expected: 555-234-4589 from dbo.AddressTest
looking into this method also: https://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string