-- This is a simple script to split comma separated string into individual elements.

SELECT level,
  CAST(REGEXP_SUBSTR(any_str, '[^,]+', 1, LEVEL) AS VARCHAR2(10)) idx_val
FROM (SELECT '123456,789012,45AG12,N50265A' any_str 
      FROM  DUAL)
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(any_str, '[^,]+'))+1
;



-- By changing this [^,] to other delimitters such as [^|] for pipe, pipe delimited 
-- string can be split into individual elements.

SELECT level,
  CAST(REGEXP_SUBSTR(any_str, '[^|]+', 1, LEVEL) AS VARCHAR2(10)) idx_val
FROM (SELECT '123456|789012|45AG12|N50265A' any_str 
      FROM  DUAL)
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(any_str, '[^|]+'))+1
;