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 minute...