SPLIT_PART with 2 delimiters with OR condition Snowflake-CodePudding

I am trying to use SPLIT_PART to retrieve value available after the symbols '.' or ':'.

Example -

data = 'first.middle.last'
split_part(data,'.',-1) 


data = 'first.middle:last' 
split_part(data,':',-1)

In both of the cases, result will be 'last'

How can I use something like split_part(data," : OR .", -1)

CodePudding user response：

You can use the REGEX function or just change all characters to one:

SET data = 'first.middle:last';
SELECT SPLIT_PART(REPLACE($data, '.', ':'),':',-1);

CodePudding user response：

You could replace : with . before parsing

split_part(replace(data,'.',':'),':',-1)

You could also nest it inside another split_part if the first one doesn't fetch anything

split_part(split_part(data,'.',-1),':',-1)