How to get the number of newlines in a cell value in PostgreSQL

Question

Lets assume I have an imaginary table named bookInfo, which contains the following values:

|id   |book_name   |description   |
-----------------------------------
|1    |book 1      |dummy         |
|2    |book 2      |harry         |
|     |            |potter        |
|     |            |Part 2        |
|3    |...

The cell value for the description column of the second record (id = 2) contains multiple newline character sequences between the "harry" and "potter" keywords like so:

harry \n potter \n part 2

Is there a way to count the number of newline characters in the description column, e.g. do something like the following:

SELECT (count new line)description FROM bookInfo WHERE id=2;

The prior query should return an integer value of 2.

Answer 1

You can compare the length of the string with newline and with out.

SELECT (LENGTH(description) - LENGTH(REPLACE(description, '\n', '')) FROM `bookinfo`

Trimmed count

If you want to count only the newlines in between (without leading and trailing newlines) you can trim them first

SELECT (LENGTH(TRIM(BOTH '\n' FROM description)) - LENGTH(REPLACE(TRIM(BOTH '\n' FROM description), '\n', ''))) FROM `bookinfo`