How to get the number of new rows in a cell value in PostgreSQL

Question

Suppose I have an imaginary table named bookInfo that 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 several sequences of newline characters between the keywords "harry" and "potter":

 harry \n potter \n part 2 

Is there a way to count the number of newline characters in the description column, for example. do the following:

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

The previous request should return an integer value of 2 .

Answer 1

To do this, you can use the SQL standard function position :

 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) 

Answer 2

One easy way:

 SELECT length(regexp_replace(your_field, E'[^\\n]', '', 'g')) from your_table 

Note: position() find the first occurrence and return its position.

Answer 3

you can find carriage returns by finding char (13) in your table field.

 select * from bookinfo where CHARINDEX(char(13),description)<>0 

Hope this will be helpful

Answer 4

You can compare the length of a string with and without a new string.

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

Trimmed amount

If you want to count only newlines between them (without leading and ending lines of a new line), you can trim them first

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