Monday, 16 March 2009

Line Count CLOB or VARCHAR2

I must be missing something, there has to be an easier way to count the number of lines in a VARCHAR2 or CLOB.
For the moment this does the trick ,but Id appreciate a heads up for a better solution.


The inner regexp_replace matches each line = '^.*$'
Replace each line with the '1' character.
Beginning character = 1
First occurance = 0
Tell oracle its a multiline string = 'm'

The outer regexp_replace removes the new lines left over.
Counting the remaining characters gives you the line count.

Ill have to look into a better solution.
Heres a much much more performant solution


replacing all CHR(10) (linefeed char) with nothing. Then compare with original length.

A query which took me 40 minutes with the first query, now takes under a second.
much faster than Oracle 11g, regexp_count as well.