Showing posts from 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 f…