Thread: PgSQL problem: How to split strings into rows

PgSQL problem: How to split strings into rows

From
Kynn Jones
Date:
I have a table X with some column K consisting of whitespace-separated words.  Is there some SELECT query that will list all these words (for the entire table) so that there's one word per row in the returned table?  E.g.  If the table X is

           K
---------------------
 foo bar baz
 quux frobozz
 eeny meeny
 miny moe

...I want the result of this query to be

 foo
 bar
 baz
 quux
 frobozz
 eeny
 meeny
 miny
 moe

How can I do this?  (I have a slight preference for solutions that will work with version 8.2, but I'm interested in any solution to the problem.)

TIA!

~K


Re: PgSQL problem: How to split strings into rows

From
Thomas Kellerer
Date:
Kynn Jones wrote on 21.01.2010 19:49:
> I have a table X with some column K consisting of whitespace-separated
> words.  Is there some SELECT query that will list all these words (for
> the entire table) so that there's one word per row in the returned
> table?  E.g.  If the table X is
>
>             K
> ---------------------
>   foo bar baz
>   quux frobozz
>   eeny meeny
>   miny moe
>
> ...I want the result of this query to be
>
>   foo
>   bar
>   baz
>   quux
>   frobozz
>   eeny
>   meeny
>   miny
>   moe
>
> How can I do this?  (I have a slight preference for solutions that will
> work with version 8.2, but I'm interested in any solution to the problem.)
>

Don't know if this will work with 8.3:

select regexp_split_to_table(k, ' ')
from x;

Thomas

Re: PgSQL problem: How to split strings into rows

From
Andreas Kretschmer
Date:
Kynn Jones <kynnjo@gmail.com> wrote:

> I have a table X with some column K consisting of whitespace-separated words.
> Is there some SELECT query that will list all these words (for the entire
> table) so that there's one word per row in the returned table?  E.g.  If the
> table X is
>
>            K
> ---------------------
>  foo bar baz
>  quux frobozz
>  eeny meeny
>  miny moe
>
> ...I want the result of this query to be
>
>  foo
>  bar
>  baz
>  quux
>  frobozz
>  eeny
>  meeny
>  miny
>  moe
>
> How can I do this?  (I have a slight preference for solutions that will work
> with version 8.2, but I'm interested in any solution to the problem.)

With 8.4:

test=*# select string_to_array('foo bar bartz', ' ');
 string_to_array
-----------------
 {foo,bar,bartz}
(1 Zeile)

Zeit: 23,390 ms
test=*# select unnest(string_to_array('foo bar bartz', ' '));
 unnest
--------
 foo
 bar
 bartz
(3 Zeilen)


With 8.2:

You have to create a function unnest:

CREATE OR REPLACE FUNCTION unnest(ANYARRAY) RETURNS SETOF ANYELEMENT
LANGUAGE SQL AS $$SELECT $1[i] FROM
generate_series(array_lower($1,1),array_upper($1,1)) i;$$;

string_to_array() should work in 8.2 (i'm not really sure, but i think,
8.2 contains this funtion)



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: PgSQL problem: How to split strings into rows

From
Ivan Sergio Borgonovo
Date:
On Thu, 21 Jan 2010 13:49:45 -0500
Kynn Jones <kynnjo@gmail.com> wrote:

> I have a table X with some column K consisting of
> whitespace-separated words.  Is there some SELECT query that will
> list all these words (for the entire table) so that there's one
> word per row in the returned table?  E.g. If the table X is
>
>            K
> ---------------------
>  foo bar baz
>  quux frobozz
>  eeny meeny
>  miny moe
>
> ...I want the result of this query to be
>
>  foo
>  bar
>  baz
>  quux
>  frobozz
>  eeny
>  meeny
>  miny
>  moe

http://www.postgresql.org/docs/current/static/functions-array.html
string_to_array

select (string_to_array('tano pino gino', ' '))[i] from
generate_series(1, 3) s(i);

You'd get the idea... to get the length of the array you've
array_length.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it