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: (view raw, whole thread or download thread mbox)
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):

bdteste=# SELECT substring(E'foo\nbar\nbaz' FROM E'^((.)+?)\n');
(1 registro)


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-2017 The PostgreSQL Global Development Group