Re: Counting the occurences of a substring within a very large text

> -----Original Message-----
> From: Albe Laurenz [mailto:laurenz(dot)albe(at)wien(dot)gv(dot)at]
> Sent: Mittwoch, 24. Juni 2015 13:44
> To: Marc Mamin; Postgres General
> Subject: RE: Counting the occurences of a substring within a very large
> text
>
> 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;

no, they both yeld the same error.

a new string functions for this would be nice, as it could certainly be implemented in a more efficient way...

BTW: the text to check is a single 350 MB error message from a log file :)

>
> Yours,
> Laurenz Albe