2008/9/23, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>:
> Mike Toews wrote:
>> Hi all,
>> I have some records that have some multiple lines within a single text
>> field. (On top of that, I think I have mixed DOS/UNIX line endings too).
>> I'm looking for two functions which can operate on a single field:
>> 1. number of lines
>> 2. a way to select the first line or to trim to the first line only
>> (the normal trim function doesn't appear to do this)
> One way, again probably not the fastest:
> SELECT (regexp_split_to_array(inputstr, E'\\n'))
> Note the extra set of parentheses. You might also want to trim() off any
> trailing \r in case of DOS line endings.
> A little C function that copied the input only up to the first newline
> would instead probably be the fastest. It'd also let you easily strip
> the trailing \r if any was present.
Use substring(string from pattern):
bdteste=# SELECT substring(E'foo\nbar\nbaz' FROM E'^((.)+?)\n');
In response to
pgsql-sql by date
|Next:||From: Nicolas Beuzeboc||Date: 2008-09-23 17:59:34|
|Subject: Re: Special grouping on sorted data.|
|Previous:||From: Louis-David Mitterrand||Date: 2008-09-23 08:18:30|
|Subject: Re: exclusion query|