Where are all the slashes? A positions() function that never came

I had file paths and wanted the directory and the file name. ClickHouse has position(haystack, needle) for the first match but nothing for the last one or for all of them, so I asked for positions(haystack, needle) returning every index as an array, to take the last element.

I was asking the wrong question. Replies showed splitByChar('/', path)[-1], a regular expression with extract, and the existing basename(path), which is the plain answer. One user suggested a family of path-processing functions instead. I said a more intuitive positions would still be nice. The issue is open, labelled feature, comp-regular-function and external, with no pull request.

On 26.4 positions still does not exist (UNKNOWN_FUNCTION), and there is no dirname. multiSearchAllPositions reports the first position of each of several needles, not every position of one. To get all positions today, build the array:

SELECT arrayFilter(i -> substring('a/b/c', i, 1) = '/', range(1, length('a/b/c') + 1));   -- [2,4]

That is one lambda call per character, which a native function would avoid. For paths, use basename. See lesser-known ClickHouse functions and features.