CODEWITHSUNDEEP
sqloracle-database
Potatooo
Potatooo

Reputation: 57

Count the number of times that new line/lines Appear

Is there any way I could read the number of times that new lines appear in return value? I tried regexp_count() but \n only counts the number of times that n appear in return values. 

SELECT REGEXP_COUNT(DATA, '\n', 1, 'i') , _DATA FROM DATA_TABLE ;

Sample below should return 3.

ABCSLLA
AAANN
SDSDS

Upvotes: 1

Views: 3808

Answers (4)

Unoembre
Unoembre

Reputation: 555

Flip REGEXP_COUNT into multi-line mode then you can use $ to count end-of-line rather than end-of-string. Detailed in Table 8.2 for multiline and 8.3 for $ here. Example

SELECT REGEXP_COUNT(DATA, '$', 1, 'm') , _DATA FROM DATA_TABLE;

One interesting side effect it that this will return 3 with your data if the last line is OR is not terminated by a newline character.

Upvotes: 1

Trung Duong
Trung Duong

Reputation: 3475

You could try

SELECT REGEXP_COUNT(DATA, CHR(10), 1, 'i') , _DATA FROM DATA_TABLE;

If you want to search for carriage returns, that would be CHR(13).
I've created a demo here

Upvotes: 0

Nitish
Nitish

Reputation: 1736

The below query should work for you.

SELECT length(regexp_replace(DATA, '[^'||chr(10)||']', '')) + 1 FROM DATA_TABLE;

Upvotes: 0

Matt M
Matt M

Reputation: 106

Assuming the values are all in one row, you can try SELECT REGEXP_COUNT(DATA, '\A', 1, 'i') , _DATA FROM DATA_TABLE ;

REF:https://www.techonthenet.com/oracle/functions/regexp_count.php

Upvotes: 0

Related Questions