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,'$[0]') AS f_0
            ,JSON_VALUE(J,'$[1]') AS f_1
            ,JSON_VALUE(J,'$[2]') AS f_2
            ,JSON_VALUE(J,'$[3]') AS f_3
            ,JSON_VALUE(J,'$[4]') AS f_4
            ,JSON_VALUE(J,'$[5]') 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!