Split is consuming the match, and splitting on it. Imagine wanting to split a path into components, but not knowing the os or path separator. You’d split on either slash.

Yeah but the fact that it's generating empty strings causes issues with knowing where your result is.

Fair! I think all our empty string vs null behavior is a mess

Yeah, although I mean, this is less related to SQL and more to how

std::regex

works. I wouldn't have retuned a null value anyway

thanks both for your insight here, this is another nuance to be aware of certainly, ultimately I have gone with REGEX_MATCH for the query I am working on for the moment as this is less confusing and something at least I'll clearly remember X weeks from now, when I inevitably come back to the SQL 😅

Yeah,

REGEX_MATCH

is much less confusing.