1

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

  • STRING_SPLIT() – Akina Feb 28 at 7:44
  • hmm, is there a way to get value element 2, 3, 4, 5, and place them into select? trying to figure that out, looking for anyway – user172734 Feb 28 at 7:47
  • This one could be used as inspiration (or a duplicate question?) dba.stackexchange.com/a/42398/2103 – Mikael Eriksson Feb 28 at 11:13
  • There will always be exactly 5 elements (and 4 commas)? How can you be sure? (Hint: Address parsing is really hard.) – Aaron Bertrand Feb 28 at 12:10
1

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) > 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy