From: | "Osvaldo Kussama" <osvaldo(dot)kussama(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Cc: | "Mike Toews" <mwtoews(at)sfu(dot)ca> |
Subject: | Resp.: Multi-line text fields |
Date: | 2008-09-23 15:57:31 |
Message-ID: | 690707f60809230857u700ed7f6s2e4c5b2dee3aceb6@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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'))[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
From | Date | Subject | |
---|---|---|---|
Next Message | Nicolas Beuzeboc | 2008-09-23 17:59:34 | Re: Special grouping on sorted data. |
Previous Message | Louis-David Mitterrand | 2008-09-23 08:18:30 | Re: exclusion query |