Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 88B002E0058 for ; Wed, 23 Apr 2008 12:19:00 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 69344-04 for ; Wed, 23 Apr 2008 12:18:58 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from zebedee.cranntara.net (zebedee.cranntara.net [91.186.7.79]) by developer.postgresql.org (Postfix) with ESMTP id 18C602E004B for ; Wed, 23 Apr 2008 12:18:57 -0300 (ADT) Received: from [62.69.60.13] (helo=localhost) by zebedee.cranntara.net with esmtpsa (TLS-1.0:RSA_AES_256_CBC_SHA1:32) (Exim 4.62) (envelope-from ) id 1JogkH-0006Us-H4; Wed, 23 Apr 2008 16:18:54 +0100 Received: from [192.168.0.32] (helo=dick.coachhouse ident=tarlika) by localhost with esmtp (Exim 4.63) (envelope-from ) id 1JogPg-0003tM-A6; Wed, 23 Apr 2008 15:57:36 +0100 Date: Wed, 23 Apr 2008 15:57:35 +0100 From: Tarlika Elisabeth Schmitz To: Alvaro Herrera Cc: Tarlika Elisabeth Schmitz , pgsql-sql@postgresql.org Subject: Re: extracting words Message-ID: <20080423155735.2755b5c7@dick.coachhouse> In-Reply-To: <20080423131303.GE4768@alvh.no-ip.org> References: <20080422222504.56bdb06d@dick.coachhouse> <20080423131303.GE4768@alvh.no-ip.org> X-Mailer: Claws Mail 3.2.0 (GTK+ 2.8.20; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200804/148 X-Sequence-Number: 30687 On Wed, 23 Apr 2008 09:13:03 -0400 Alvaro Herrera wrote: > Tarlika Elisabeth Schmitz wrote: > > > I need to extract the words from department_name and product_name > > (words are separated by spaces) and get something like: > > > > 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 > > regexp_split_to_table is handy (8.3 only): > > select pk, regexp_split_to_table(product_name, '[& ]+') from products > union > select pk, regexp_split_to_table(dept_name, '[& ]+') from departments; > > Add joins as desired. This is neat! Unfortunately, on the machine that I need this, we are running 7.4. -- Best Regards, Tarlika Elisabeth Schmitz A: Because it breaks the logical sequence of discussion Q: Why is top posting bad?