2008/9/23, Craig Ringer <craig@postnewspapers.com.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'))[1]
>
> 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):
http://www.postgresql.org/docs/current/interactive/functions-string.html
bdteste=# SELECT substring(E'foo\nbar\nbaz' FROM E'^((.)+?)\n');substring
-----------foo
(1 registro)
Osvaldo
