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

Re: Splitting text column to multiple rows

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Andrus <kobruleht2(at)hot(dot)ee>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Splitting text column to multiple rows
Date: 2010-03-28 18:03:44
Message-ID: 162867791003281103le4ec4d2k6ceafff851d24eb4@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
Hello

try:

CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement as $$
  SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

pavel(at)postgres:5481=# select unnest(string_to_array('23,2,3,4,5',','));
 unnest
--------
 23
 2
 3
 4
(4 rows)

regards
Pavel Stehule



2010/3/28 Andrus <kobruleht2(at)hot(dot)ee>:
> TEXT column contains multi-line text.
> How to split it to multiple rows so that every line is in separate row ?
> Code below should return two rows,
>
> Line 1
> Line 2
>
> Solution should work starting at 8.1
>
> Should generate_series or pgsql procedure used or any other idea?
>
> Andrus.
>
>
> create temp table test ( test text ) on commit drop;
> insert into test values( 'Line 1' ||chr(13)||'Line2');
>
> create temp table test2 ( test text ) on commit drop;
> -- todo: split test to multiple rows
> insert into test2 select * from test;
> select * from test2;
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

In response to

Responses

pgsql-general by date

Next:From: Alban HertroysDate: 2010-03-28 18:22:26
Subject: Re: How to perform text merge
Previous:From: AndrusDate: 2010-03-28 17:43:14
Subject: Splitting text column to multiple rows

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