regexp_match()? You aren't returning a set of matches so use the scalar function. Probably still need to deal with null properly.
Which works perfectly, except performance is 50% slower on a dataset of around 20k
Is there a better solution
Probably not materially - it seems like you have to do it in two parts - one to break apart the paths and then one to handle the fact that you want the part subsequent to the final period (i.e., the file extension) in its own array cell. Choosing the best function for each job will hopefully improve matters at least a bit.
