vegas red vegas red - 5 months ago 9
SQL Question

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

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

You can use the SQL-standard position function for this:

craig=> SELECT position( E'\r' in E'abc\rdef\rghi' );
 position 
----------
        4
(1 row)

or:

craig=> SELECT position( chr(13) in E'abc\rdef\rghi' );
 position 
----------
        4
(1 row)