CODEWITHSUNDEEP
sqlsplitsnowflake-cloud-data-platform
Lakm
Lakm

Reputation: 49

SPLIT_PART with 2 delimiters with OR condition Snowflake

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)

Upvotes: 2

Views: 3245

Answers (2)

Rajat
Rajat

Reputation: 5793

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)

Upvotes: 2

Michael Golos
Michael Golos

Reputation: 2059

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

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

Upvotes: 2

Related Questions