How do I remove selected words from text field?
От | Frank Bax |
---|---|
Тема | How do I remove selected words from text field? |
Дата | |
Msg-id | BLU0-SMTP789E97372809C06816956AACC70@phx.gbl обсуждение исходный текст |
Ответы |
Re: How do I remove selected words from text field?
|
Список | 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?
В списке pgsql-sql по дате отправления: