Re: extracting words

From: Tarlika Elisabeth Schmitz <postgresql(at)numerixtechnology(dot)de>
To: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: extracting words
Date: 2008-04-24 16:12:41
Message-ID: 20080424171241.60b030c0@dick.coachhouse
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

On Wed, 23 Apr 2008 07:18:44 +0200
"A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)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?

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Nacef LABIDI 2008-04-24 16:39:06 Re: First day of month, last day of month
Previous Message Fernando Hevia 2008-04-24 16:07:19 Re: First day of month, last day of month