I have used the function regexp_substr(STR,'[^|]+',1,2),
it works well at some cases but for null scenarios, it gives the third value,
1|CAT|DOG
3|HARRY|GOAT|STACK
6||LION|TIGER --this row should return null, but the above function gives me LION
Expected Output:
CAT
HARRY
(null)