Skip site navigation (1) Skip section navigation (2)

Resp.: Multi-line text fields

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 (view raw or flat)
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

In response to

pgsql-sql by date

Next:From: Nicolas BeuzebocDate: 2008-09-23 17:59:34
Subject: Re: Special grouping on sorted data.
Previous:From: Louis-David MitterrandDate: 2008-09-23 08:18:30
Subject: Re: exclusion query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group