From: | Frank Bax <fbax(at)sympatico(dot)ca> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | How do I remove selected words from text field? |
Date: | 2010-06-25 22:23:48 |
Message-ID: | BLU0-SMTP789E97372809C06816956AACC70@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I'm not quite sure how to ask for the query I want, so let's start with
data:
create table t1 (i int, val 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');
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');
t1.val will contain "words" separated by blanks. It might be better if
each "word" were a separate row in another table; but that's not how the
legacy database was built. I understand this can be simulated by:
select i,a[s] from (select i, generate_subscripts(string_to_array(val,'
'),1) as s, string_to_array(val,' ') as a from t1) foo;
In my "real life" situation, the "words" are not single letters.
I'd like to write a function that removes selected "words" from t1.val
based on select on t2.
In the above example; let's exclude all vowels, so I end up with:
1 'B C D'
2 'B D F'
3 'G H I J'
For some "words" in val; there may not be a row when joining to t2.q;
these words must be included in final result. In the above example;
there is no row in t2 where q="I"; so it is included in result.
How do I write such a function? Can it be done with SQL only?
From | Date | Subject | |
---|---|---|---|
Next Message | Lee Hachadoorian | 2010-06-25 22:53:01 | Round integer division |
Previous Message | Ireneusz Pluta | 2010-06-25 19:33:55 | Re: Average of Array? |