From: Frank Bax <fbax@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: How do I remove selected words from text field?
Date: Thu, 01 Jul 2010 19:25:26 -0400
Message-ID: <BLU0-SMTP6385CC10DC27B119CBC3FBACCD0@phx.gbl> (raw)
In-Reply-To: <AANLkTim8AiMOEQhd8vkBlc7p7JqYToqH140CJ4v_NWk8@mail.gmail.com>
References: <BLU0-SMTP789E97372809C06816956AACC70@phx.gbl>
<AANLkTil2rYpIoIisIGZpEmpIILCV9syw_yGzuN89drOG@mail.gmail.com>
<BLU0-SMTP15F262059DF646A24B8853ACC80@phx.gbl>
<BLU0-SMTP7888ACA3DFCEDA21C88B69ACCD0@phx.gbl>
<AANLkTim8AiMOEQhd8vkBlc7p7JqYToqH140CJ4v_NWk8@mail.gmail.com>
Osvaldo Kussama wrote:
> 2010/7/1 Frank Bax <fbax@sympatico.ca>:
>> 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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fbax@sympatico.ca
Subject: Re: How do I remove selected words from text field?
In-Reply-To: <BLU0-SMTP6385CC10DC27B119CBC3FBACCD0@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox