If you are not familiar with the ‘WITH’ statement, I am just using it to mock up a table that has a row_id and a clob value – feel free to run this bit (from the select within the WITH clause) in isolation to get the rows with the CR/LF’s.
There are several ways, if you need to extract each row, I expect as separate text you may find working with Option 3 the most productive.
Option 1:
Using LENGTH is just working out how many CHR(13)’s there are
WITH clob_tab AS
(SELECT ROWNUM AS table_id, clob_val
FROM (SELECT 'ZOG' || CHR (13) || CHR (10) || 'GIBBENS' AS clob_val
FROM DUAL
UNION ALL
SELECT 'ZOG' || CHR (13) || CHR (10)
|| 'ATE' || CHR (13) || CHR (10)
|| 'ALL' || CHR (13) || CHR (10)
|| 'OF HIS DINNER' AS clob_val
FROM DUAL
)
)
SELECT table_id, LENGTH (clob_val) - LENGTH (REPLACE (clob_val, CHR (13), '')) + 1
FROM clob_tab
Option 2:
WITH clob_tab AS
(SELECT ROWNUM AS table_id, clob_val
FROM (SELECT 'ZOG' || CHR (13) || CHR (10) || 'GIBBENS' AS clob_val
FROM DUAL
UNION ALL
SELECT 'ZOG' || CHR (13) || CHR (10)
|| 'ATE' || CHR (13) || CHR (10)
|| 'ALL' || CHR (13) || CHR (10)
|| 'OF HIS DINNER' AS clob_val
FROM DUAL
)
)
select table_id, LENGTH (clob_val) - LENGTH (REPLACE (clob_val, CHR (10), '')) + 1
from clob_tab
Option 3:
If you’ve not used CONNECT BY LEVEL before, it is worth reading and working through the example in the oracle documentation..
WITH clob_tab AS
(SELECT ROWNUM AS table_id, clob_val
FROM (SELECT 'ZOG' || CHR (13) || CHR (10) || 'GIBBENS' AS clob_val
FROM DUAL
UNION ALL
SELECT 'ZOG' || CHR (13) || CHR (10)
|| 'ATE' || CHR (13) || CHR (10)
|| 'ALL' || CHR (13) || CHR (10)
|| 'OF HIS DINNER' AS clob_val
FROM DUAL
)
)
SELECT table_id,
MAX (row_num)
FROM (SELECT table_id,
clob_val,
LEVEL AS row_num
FROM clob_tab
CONNECT BY LEVEL <= LENGTH (clob_val) - LENGTH (REPLACE (clob_val, CHR (10), '')) + 1)
GROUP BY table_id
