ORACLE QUERY TO SPLIT DELIMITED COLUMN VALUE INTO ROWS

We can use the below 2 ways to split a delimited column value into rows:

1. Using XMLSEQUENCE:

WITH T AS (

           SELECT ROW_WID,X_SO_LINE_ID,X_SO_LINE_ID AS STR FROM W_WORKORDER_D

          )

SELECT  ROW_WID WORKORDER_WID,X_SO_LINE_ID,EXTRACTVALUE(VALUE(X), '/B') LINE_ID

  FROM  T,TABLE(XMLSEQUENCE(XMLTYPE('<A><B>' || REPLACE(STR, '|', '</B><B>') || '</B></A>' ).EXTRACT('/*/*')) ) X

  ORDER BY X_SO_LINE_ID

2. Using Regular expression

 

SELECT REGEXP_SUBSTR (X_SO_LINE_ID,'[^|]+',1,LEVEL) LINE_ID,

W_WORKORDER_D.ROW_WID WORKORDER_WID FROM W_WORKORDER_D

WHERE 1=1

CONNECT BY LEVEL <=REGEXP_COUNT (X_SO_LINE_ID,'[^|]+')

              CONNECT BY LEVEL <=REGEXP_COUNT (X_SO_LINE_ID,'[^|]+')

Note: '|' is the delimitter in above queries 

           The performance of the 1st query will be good compared to the second one