Re: How do I remove selected words from text field?

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

Re: How do I remove selected words from text field?

2010/7/1 Frank Bax <fbax <at> 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;
>

Re: How do I remove selected words from text field?

Osvaldo Kussama wrote:
> 2010/7/1 Frank Bax <fbax <at> 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;
>>

Re: How do I remove selected words from text field?

On 07/02/10 03:25, Frank Bax wrote:
> Osvaldo Kussama wrote:
>> 2010/7/1 Frank Bax <fbax <at> 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');

first of all rebuild the table.
explode these strings by space char
and put into another table for convenient use

In other words
construct proper data scheme PRIOR TO USE it.

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Domains based on composite types.

How to store Byte strings into a table.

Re:

On 07/05/10 09:57,  wrote:
> Hi,
>
> How can I store Byte strings into a postgresql database.
> Is there any special command to store it. How will be the sql query.

there is only '\0' byte incapable to input-output.
so u have to have it escaped at all costs _AND NOTHING MORE_.

"escaped" doesn't mean "prefixed with backslash"
("backslash method" cause a zero-byte to pass SQL parser an to be 
actually stored, BUT
the output will be corrupted, because of this zero-byte will be actually 
output)

You may use the BYTEA type
(similar to the TEXT but with different input-output) which effectively 
escapes zero-byte and a lot of other completely harmless bytes as well 
(probably to reach a better overhead)

Or you may introduce a pair of your own escape rules.

Unfortunately there are no way to influence The Pg Developers to get rid 
of the nasty god damned CSTRING off the input/output operations.

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re:

Re:

Hello

use a LO interface

http://www.postgresql.org/docs/8.4/static/lo-interfaces.html

exact form depends on language that you are use.

Regards

Pavel Stehule

2010/7/5 Trinath Somanchi <trinath.somanchi <at> gmail.com>:
> Hi,
>
> I'm new in using BLOB. How will the insert for storing very large byte
> strings into a column  of data type Blob.
>
> On Mon, Jul 5, 2010 at 11:42 AM, silly sad <sad <at> bankir.ru> wrote:
>>
>> On 07/05/10 09:57,  wrote:
>>>
>>> Hi,
>>>
>>> How can I store Byte strings into a postgresql database.
>>> Is there any special command to store it. How will be the sql query.
>>
>> there is only '\0' byte incapable to input-output.
>> so u have to have it escaped at all costs _AND NOTHING MORE_.
>>
>> "escaped" doesn't mean "prefixed with backslash"
>> ("backslash method" cause a zero-byte to pass SQL parser an to be actually
>> stored, BUT
>> the output will be corrupted, because of this zero-byte will be actually
>> output)
>>
>> You may use the BYTEA type
>> (similar to the TEXT but with different input-output) which effectively
>> escapes zero-byte and a lot of other completely harmless bytes as well
>> (probably to reach a better overhead)
>>
>> Or you may introduce a pair of your own escape rules.
>>
>> Unfortunately there are no way to influence The Pg Developers to get rid
>> of the nasty god damned CSTRING off the input/output operations.
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
>
>
> --
> Regards,
> ----------------------------------------------
> Trinath Somanchi,
>

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re:

On 07/05/10 10:30, Trinath Somanchi wrote:
> Hi,
>
> I'm new in using BLOB. How will the insert for storing very large byte
> strings into a column  of data type Blob.

i didn't advice you to use BLOB.

you may store a string as long as 2GB at any TEXT or BYTEA field.

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql