These days of PowerQuery, Spark and other query tools there are quite a range of options available to split delimited fields, but what if you have a table in your database with fields of delimited text and no way to influence the incoming data.
A real bad-boy table, with a badge of “I’m 0NF” on its datapages. A little (or big) rotten apple in the basket of relational-ism. Let’s get him treated.
There are various ‘easy’ methods to do this. A view might come in mind with a set of substrings, length – index of etc… Quiet quickly this becomes unreadable and not reliable.
SELECT SUBSTRING([delimited field], 1, CHARINDEX(',', [delimited field]) - 1, ...
The method I’d like to explore here is by using JSON query engine in SQLServer. What a detour is this! But hang in there, the beauty and performance might pleasantly surprise you.
SELECT * FROM [STG].[0NF Table] A CROSS APPLY ( SELECT JSON_VALUE(J,'$') AS f_0 ,JSON_VALUE(J,'$') AS f_1 ,JSON_VALUE(J,'$') AS f_2 ,JSON_VALUE(J,'$') AS f_3 ,JSON_VALUE(J,'$') AS f_4 ,JSON_VALUE(J,'$') AS f_5 FROM (VALUES ('["'+replace([delimited field],'|','","')+'"]')) A(J) ) B
Try it out, on 100.000 records if you must.
Copy the result of this query into a table called [1NF Table] and you’ll already feel more at ease. You can now do joins and other useful operations. You’re welcome!