On Wed, 23 Apr 2008 07:18:44 +0200 "A. Kretschmer" <[EMAIL PROTECTED]> 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? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
