Quickest way to retrieve nth element in a delimited list?
What is the quickest way to retrieve the nth element in a delimited list, using Oracle 10g or above?
If a table has a column containing the string 'cat,dog,mouse,rat,bat,car,door,lock', what is the quickest way to get, for example, the 4th element and return 'rat'.
We have code currently which does a loop and lots of instrings plus counting. It's slow and messy. I am after something more elegant (and efficient!), and capable of being used as a generic function into which a delimited string is passed, together with the number of the required element, and having that element alone returned.