Refactoring a delimited column into a one-to-many table lookup


1 min read

Note: MS SQL 2016 and above only solution below.

Dealing with a table where someone's placed multiple string values into a single varchar(max) field.

Something like this:


Id       Breakdown
1        Boop
2        Scooby
3        Boop:Scooby:Shewop
4        Scooby:Boop

and on it goes. The lookup table is defined like so:


PK Name varchar(50) not null
SortOrder int not null

Not sure why a PK is on a varchar(50) but that's what I'm working with. We'll be excising Breakdown on the table and getting rid of it to create a many-to-many relationship between OurData and Lookup

First step is to make sure all possible values from OurData.Breakdown are in our lookup table. Once that's done we create a table OurData_Lookup defined:


PK Id int not null identity
FK OurDataId int
FK LookupName varchar(50)

Now we need to transfer the data over, and why this post exists as it uses some SQL I haven't used before. Doesn't happen very often :)

INSERT INTO OurData_Lookup (OurDataId, LookupName)
FROM OurData CROSS APPLY string_split(OurData.Breakdown, ':')

Pretty darn slick if you ask me. From there we can remove the Breakdown column from OurData and blamo, refactor is complete