Thread: Remove Duplicate Words from a field

On 16/05/2011 13:34, Sukuchha Shrestha wrote:
> Dear All,
>
> I am new to Postgresql. I have a field with lots of dublicate words and
> i want to remove any dublicate word from that field.
>
> For example, if i have a field with a string " one, one, two, two,
> three", how would i get " one, two, three" only ?

Maybe a regular expression, using regexp_replace()?

   http://www.postgresql.org/docs/8.4/static/functions-string.html

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

I would use a plpython or plperl function

In python you can split the word into a list and then get unique items and put it back in a string, for example.

Sim

On 05/16/2011 03:34 PM, Sukuchha Shrestha wrote:

Dear All,

I am new to Postgresql. I have a field with lots of  dublicate words and i want to remove any dublicate word from that field.

For example, if i have a field with a string " one, one, two, two, three", how would i get " one, two, three" only ?

Any help is much apprecieated !

Sukuchha  

Hello

2011/5/16 Sukuchha Shrestha <sukuchha@yahoo.de>:
> Dear All,
> I am new to Postgresql. I have a field with lots of  dublicate words and i
> want to remove any dublicate word from that field.
> For example, if i have a field with a string " one, one, two, two, three",
> how would i get " one, two, three" only ?
> Any help is much apprecieated !
> Sukuchha


postgres=# CREATE OR REPLACE FUNCTION uniq_worlds(text)
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT DISTINCT trim(x) FROM
unnest(string_to_array($1,',')) x),', ')
$$ LANGUAGE sql;
CREATE FUNCTION
Time: 14.281 ms
postgres=# SELECT uniq_worlds('one, two, three, one');
   uniq_worlds
─────────────────
 three, two, one
(1 row)

Time: 0.817 ms

Regards

Pavel Stehule

2011/5/16 Sukuchha Shrestha <sukuchha@yahoo.de>:
> Hi Pavel,
> Thanks for your code. I am using postgresql 8.3 so dont have unnest function
> in my postgresql. But i found equivalend code for unnest in internet.
> Afterwards i tried your code, and it worked  !!
> Thanks for the help ! you saved me from being a dead man.
> Sukuchha

nice to help you

:)

Regards

Pavel

> ________________________________
> Von: Pavel Stehule <pavel.stehule@gmail.com>
> An: Sukuchha Shrestha <sukuchha@yahoo.de>
> CC: pgsql-general@postgresql.org
> Gesendet: Montag, den 16. Mai 2011, 15:06:14 Uhr
> Betreff: Re: [GENERAL] Remove Duplicate Words from a field
>
> Hello
>
> 2011/5/16 Sukuchha Shrestha <sukuchha@yahoo.de>:
>> Dear All,
>> I am new to Postgresql. I have a field with lots of  dublicate words and i
>> want to remove any dublicate word from that field.
>> For example, if i have a field with a string " one, one, two, two, three",
>> how would i get " one, two, three" only ?
>> Any help is much apprecieated !
>> Sukuchha
>
>
> postgres=# CREATE OR REPLACE FUNCTION uniq_worlds(text)
> RETURNS text AS $$
> SELECT array_to_string(ARRAY(SELECT DISTINCT trim(x) FROM
> unnest(string_to_array($1,',')) x),', ')
> $$ LANGUAGE sql;
> CREATE FUNCTION
> Time: 14.281 ms
> postgres=# SELECT uniq_worlds('one, two, three, one');
>   uniq_worlds
> ─────────────────
> three, two, one
> (1 row)
>
> Time: 0.817 ms
>
> Regards
>
> Pavel Stehule
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>