If you are like me, you probably use SubField a lot. I had used it for years before I stumbled across one of its most powerful features in someone else’s code. If you do not provide the position parameter, it will expand a record for each value.

I’ve worked with a few older databases that liked to store arrays within a single column. This is a perfect example of where it comes in handy.

Data:
Load 
    RowNo() as ID,
    Replace(Replace(Qty,'(','['),')',']') as Qty
Inline [
Qty
(10,12,15,20)
(25,25,25,25)
(1080,720,2,3)
](delimiter is \t);


Expanded:
Load
    RowNo() as LineID,
    ID,
    Qty,
    SubField(PurgeChar(Qty,'[]'),',') as Quantity
Resident
    Data;

Drop Table Data;

Which results in: