Category Archives: Oracle

Regex to find count from CLOB field in oracle

eval function –
———————-

SELECT xmlquery(REPLACE( '1+2+5', '/', ' div ' ) returning content) FROM dual

regex to find the count from CLOB –
———————————-

CASE WHEN LST_LOG LIKE '%logical record count%' THEN regexp_replace(lst_log,'(.*)(logical record count )([0-9]{1,})(.)(.*)','\3',1,0, 'mn') END AS REC_COUNT,

sum regex counts –
———————-

WITH t1(col) AS(
SELECT 'Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Save data point reached - logical record count 10000.
Load completed - logical record count 36342.
' FROM dual
)
SELECT xmlquery(REPLACE(REPLACE(REPLACE(regexp_replace(t1.col, '((.*)(logical record count )([0-9]{1,})(.)(.*))|.', ',\4\5'), ',', ''), '.', '+') || '0+0', '/', ' div ') returning content) res
FROM t1;