In PostgreSQL, you can use SPLIT_PART function to returns n-th item from a delimited string:

PostgreSQL:

  -- Return 3rd item from string delimited by comma
  SELECT SPLIT_PART('San Francisco,Los Angeles,London,Monaco', ',', 3);
  # London

Note that PostgreSQL SPLIT_PART function returns empty string (not NULL) if the delimiter of n-th item was not found.

You can implement SPLIT_PART function in Oracle using an user-defined function:

Oracle:

  CREATE OR REPLACE FUNCTION split_part (string VARCHAR2, delimiter VARCHAR2, n NUMBER)
    RETURN VARCHAR2
  IS
    v_start NUMBER(5) := 1;
    v_end NUMBER(5);
  BEGIN
    -- Find the position of n-th -1 delimiter
    IF n > 1 THEN
      v_start := INSTR(string, delimiter, 1, n - 1);
 
       -- Delimiter not found
       IF v_start = 0 THEN
          RETURN NULL;
       END IF;
 
       v_start := v_start + LENGTH(delimiter);
 
    END IF;
 
    -- Find the position of n-th delimiter
    v_end := INSTR(string, delimiter, v_start, 1);
 
    -- If not found return until the end of string
    IF v_end = 0 THEN
      RETURN SUBSTR(string, v_start);
    END IF;
 
    RETURN SUBSTR(string, v_start, v_end - v_start);
  END;
  /

Now you can use this function in Oracle as follows:

  -- Return 3rd item from string delimited by comma
  SELECT SPLIT_PART('San Francisco,Los Angeles,London,Monaco', ',', 3) FROM dual;
  # London

Note that since Oracle does not support empty strings the function returns NULL (not empty string as SPLIT_PART in PostgreSQL) if the delimiter of n-th item was not found.

• PostgreSQL to Oracle Migration Reference

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - March 2013.