Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id CBC4B632E16 for ; Thu, 1 Jul 2010 20:25:39 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 37670-04 for ; Thu, 1 Jul 2010 23:25:31 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from blu0-omc2-s13.blu0.hotmail.com (blu0-omc2-s13.blu0.hotmail.com [65.55.111.88]) by mail.postgresql.org (Postfix) with ESMTP id C007D6324D7 for ; Thu, 1 Jul 2010 20:25:32 -0300 (ADT) Received: from BLU0-SMTP63 ([65.55.111.71]) by blu0-omc2-s13.blu0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Thu, 1 Jul 2010 16:25:30 -0700 X-Originating-IP: [64.231.91.225] X-Originating-Email: [fbax@sympatico.ca] Message-ID: Received: from t60p.bax.ca ([64.231.91.225]) by BLU0-SMTP63.blu0.hotmail.com over TLS secured channel with Microsoft SMTPSVC(6.0.3790.4675); Thu, 1 Jul 2010 16:25:29 -0700 Date: Thu, 01 Jul 2010 19:25:26 -0400 From: Frank Bax User-Agent: Thunderbird 2.0.0.22 (X11/20090704) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: How do I remove selected words from text field? References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 01 Jul 2010 23:25:29.0658 (UTC) FILETIME=[B19761A0:01CB1974] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.801 tagged_above=-5 required=5 tests=BAYES_50=0.8, MSGID_FROM_MTA_HEADER=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201007/3 X-Sequence-Number: 34316 Osvaldo Kussama wrote: > 2010/7/1 Frank Bax : >> Create some tables; then add some data: >> >> create table t1 (i int, v varchar); >> insert into t1 values(1,'A B C D'); >> insert into t1 values(2,'B D E F'); >> insert into t1 values(3,'G H I J'); >> insert into t1 values(4,'E'); >> create table t2 (q varchar, z varchar); >> insert into t2 values('A','vowel'); >> insert into t2 values('B','consonant'); >> insert into t2 values('E','vowel'); >> insert into t2 values('K','consonant'); >> >> I am trying to write a function which will: >> * split the argument into "words" (separated by blanks); >> * remove words that meet a certain condition in another table >> (in this example 'vowel'); >> * reassemble "words" into a string; >> * return the result >> This query does that job (Thanks Osvaldo): >> >> SELECT i, array_to_string(array_agg(word), ' ') "new-v" FROM >> (SELECT * FROM (SELECT i, regexp_split_to_table(v, E'\\s+') AS word FROM t1) >> bar >> LEFT OUTER JOIN t2 ON (bar.word=t2.q) >> WHERE z IS DISTINCT FROM 'vowel') foo >> GROUP BY i; >> >> i | new-v >> ---+--------- >> 1 | B C D >> 3 | G H I J >> 2 | B D F >> (3 rows) >> >> >> When I try to create a function to do the same thing; it only works for >> (4,'E') and not the other tuples. >> >> CREATE OR REPLACE FUNCTION notvowel(text) RETURNS text AS $$ >> SELECT array_to_string(array_agg(word),' ') FROM >> (SELECT * FROM (SELECT regexp_split_to_table($1, E'\s+') AS word) bar > > Use E'\\s+' or E'[[:space:]]+' in regexp_split_to_table function. Both of these produce incorrect results... i | v | notvowel ---+---------+---------- 1 | A B C D | C 2 | B D E F | B 3 | G H I J | G 4 | E | (4 rows)