Date: Thu, 24 Apr 2008 17:12:41 +0100
From: Tarlika Elisabeth Schmitz <postgresql@numerixtechnology.de>
To: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: extracting words
Message-ID: <20080424171241.60b030c0@dick.coachhouse>
In-Reply-To: <20080423051842.GA8401@a-kretschmer.de>
References: <20080422222504.56bdb06d@dick.coachhouse>
	<20080423051842.GA8401@a-kretschmer.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Wed, 23 Apr 2008 07:18:44 +0200
"A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote:

> am  Tue, dem 22.04.2008, um 22:25:04 +0100 mailte Tarlika Elisabeth
> Schmitz folgendes:
> > TABLE product: product_pk,  product_name, department_fk
> > TABLE product: department_pk,  department_name
> > 
> > ...
> > 
> > 
> > I need to extract the words from department_name and product_name
> > (words are separated by spaces) ...
> 
> A little function (author: David Fetter)
> 
> -- split a string to rows, by David Fetter
> CREATE OR REPLACE FUNCTION split_to_rows(TEXT, TEXT) RETURNS SETOF
> TEXT STRICT
> LANGUAGE sql
> AS $$
>     SELECT (string_to_array($1, $2))[s.i]
>     FROM generate_series(
>         1,
>         array_upper(string_to_array($1, $2), 1)
>     ) AS s(i);
> $$;
> 
> 
> And now:
> 
> test=*# select * from product ;
>  id |       val1       |       val2
> ----+------------------+-------------------
>   1 | cakes & desserts | apple crumble
>   2 | cakes & desserts | cheese cake
>   3 | starters & soups | french onion soup
> (3 rows)
> 
> test=*# select id, split_to_rows(replace(val1 || ' ' || val2,'
> &',''),' ') as col1 from product; id |   col1
> ----+----------
>   1 | cakes
>   1 | desserts
>   1 | apple
>   1 | crumble
>   2 | cakes
>   2 | desserts
>   2 | cheese
>   2 | cake
>   3 | starters
>   3 | soups
>   3 | french
>   3 | onion
>   3 | soup
> (13 rows)


Many thanks! This is ingenious!

Before I ask any stupid questions such as "what does replace (text, '
&', '') do?": where can I find a more detailed description of the
string functions?

I would like to treat all white space (new-line, tab, space) as
word separator.

Will the above work on 7.4?


--


Best Regards,

Tarlika Elisabeth Schmitz


A: Because it breaks the logical sequence of discussion
Q: Why is top posting bad?