Re: PgSQL problem: How to split strings into rows

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