From: Tarlika Elisabeth Schmitz <postgresql@numerixtechnology.de>
To: A. Kretschmer <andreas.kretschmer@schollglas.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: extracting words
Date: Thu, 24 Apr 2008 17:12:41 +0100
Message-ID: <20080424171241.60b030c0@dick.coachhouse> (raw)
In-Reply-To: <20080423051842.GA8401@a-kretschmer.de>
References: <20080422222504.56bdb06d@dick.coachhouse>
<20080423051842.GA8401@a-kretschmer.de>
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@numerixtechnology.de, andreas.kretschmer@schollglas.com
Subject: Re: extracting words
In-Reply-To: <20080424171241.60b030c0@dick.coachhouse>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox