Split String in Oracle?

Question

I have a select query in Postgres with SPLIT_PART() function which works fine in postgres

SELECT SPLIT_PART(totalscore, '_', 1) highlow,
       SPLIT_PART(totalscore, '_', 2) score,
       score_desc
  FROM student_score
 WHERE (totalscore LIKE 'HN%' OR totalscore LIKE 'LN%')

http://sqlfiddle.com/#!15/877966/4

The SPLIT_PART() is not working in Oracle Could please let me know how to achieve the same in Oracle as SPLIT_PART() is not working .

http://sqlfiddle.com/#!4/042f62/5

Answer 1

You can use REGEXP_SUBSTR() with [^_]+ patterns such as

SELECT REGEXP_SUBSTR(totalscore, '[^_]+') AS highlow, 
       REGEXP_SUBSTR(totalscore, '[^_]+$') AS score,
       score_desc
  FROM student_score
WHERE REGEXP_LIKE(totalscore,'^HN_|^LN_')

Demo

considering the whole dataset contains one and only one underscore per each value of the column.