SQL Split Address Line into Separate Columns
STRING_SPLIT() is probably best in SQL Server 2016. I've tested against 1 million rows and returns results in 12 seconds (fairly contrived test).

You can use the PIVOT operator to produce the columns from your rows following the split as there are a known number of elements.

Code to setup

CREATE TABLE #Table (ID INT IDENTITY, StreetAddress VARCHAR(MAX))

DECLARE @I INT = 1000000

WHILE (@I) &gt; 0
BEGIN
       INSERT INTO #Table (StreetAddress) VALUES ('123 Fake St, BigCity, My State, 12345')
       SET @I = @I -1
END


Code to retrieve split address elements

SELECT ID,
       [1] AS Street,
       [2] AS City, 
       [3] AS State, 
       [4] AS Code
FROM 
(
       SELECT ID, value, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Rn
       FROM #Table
       CROSS APPLY STRING_SPLIT(REPLACE(StreetAddress, ', ', ','), ',')
) src
PIVOT
(
       MAX(Value) FOR Rn IN ([1], [2], [3], [4])
) pvt


You can probably improve performance by eliminating the REPLACE in STRING_SPLIT, but you'll then need to TRIM your columns to remove whitespace.