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

> -----Original Message-----
> From: Albe Laurenz [mailto:laurenz.albe@wien.gv.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