Re: Counting the occurences of a substring within a very large text - Mailing list pgsql-general

Marc Mamin wrote:
> I'd like to count the number  linebreaks within a string,
> but I get a memory allocation error when using regexp_matches or regexp_split_to_table.
> 
> Any idea for an alternative to this problem  ?
> 
> select count(*)-1 from
> (  select regexp_split_to_table(full_message,'(\n)', 'g')
>    from mytable
>    where id =-2146999703
> )foo;
> 
> ERROR:  invalid memory alloc request size 1447215584

Does any of these two work:

SELECT length(regexp_replace(full_message, '[^\n]', '', 'g'))
FROM mytable
WHERE id = -2146999703;

or

SELECT length(full_message) - length(replace(full_message, E'\n', ''))
FROM mytable
WHERE id = -2146999703;

Yours,
Laurenz Albe