Thread: PgSQL problem: How to split strings into rows

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

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

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°

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