SUBSTRING_INDEX() in Snowflake-CodePudding

What is the exact duplicate function of MySQL SUBSTRING_INDEX() in Snowflake??

I found SPLIT_PART() in Snowflake but this is not the exact same of SUBSTRING_INDEX().

E.g SUBSTRING_INDEX("www.abc.com", ".", 2); returns www.abc

all the left side substring after 2nd delimiter '.'

but

SPLIT_PART("www.abc.com", ".", 2); return abc

it splits 1st then only returns the split part of a string.

How can I use SUBSTRING_INDEX() in the same way as MySQL in Snowflake

CodePudding user response：

You may use REGEXP_SUBSTR here:

SELECT REGEXP_SUBSTR('www.abc.com', '^[^.] \.[^.] ');

Here is a

How does it works?

STRTOK_TO_ARRAY - make an array from string
ARRAY_SLICE - take the parts from 0 to n
ARRAY_TO_STRING - convert array back to string using '.' as delimeter

In steps:

SELECT 
  s.c,
  STRTOK_TO_ARRAY(s.c, '.')   AS arr,
  ARRAY_SLICE(arr, 0, 2)      AS slice,
  ARRAY_TO_STRING(slice, '.') AS result
FROM (VALUES ('www.abc.com')) AS s(c);