Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-sql by date

Next:From: Nacef LABIDIDate: 2008-04-24 16:39:06
Subject: Re: First day of month, last day of month
Previous:From: Fernando HeviaDate: 2008-04-24 16:07:19
Subject: Re: First day of month, last day of month

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group