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.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions130.htm#SQLRF06302
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.
>EDIT
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.
For the moment this does the trick ,but Id appreciate a heads up for a better solution.
length(regexp_replace(regexp_replace(TEXTVALUE,'^.*$','1',1,0,'m'),'\s',''))
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions130.htm#SQLRF06302
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.
>EDIT
Heres a much much more performant solution
LENGTH(TEXTVALUE) - LENGTH(replace(TEXTVALUE,chr(10))))
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.