Posts

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.


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 f…