CODEWITHSUNDEEP
sqlpostgresql
vegas red
vegas red

Reputation: 269

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.

Upvotes: 10

Views: 5021

Answers (4)

WKT
WKT

Reputation: 263

One simple way:

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

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

Upvotes: 4

David Boho
David Boho

Reputation: 2716

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`

Upvotes: 4

Craig Ringer
Craig Ringer

Reputation: 324701

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)

Upvotes: -1

mrida
mrida

Reputation: 1157

you may look for carriage return by finding char(13) in your table field.

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

Hope this is helpful

Upvotes: -1

Related Questions