Sorting CSV string and removing Duplicates

On Mon, Jul 27, 2015 at 12:53 PM, Alex Magnum <magnum11200@gmail.com> wrote:

Hello,

I have a csv string in a text field that is unsorted and contains
duplicates.
Is there a simple way to remove these and sort the string.

E.g
2,18,20,23,1,27,1,2,8,16,17,18,20,22,23,27

i tried string to array and unique but that did not work...
Any suggestions on how to do this without writing a function?

Any help is appreciated.

Are you looking for this.

postgres=# SELECT unnest(string_to_array(t, ',')) from test group by 1;
unnest
--------
2
18
8
20
22
16
27
17
23
1
(10 rows)

Regards,
Dinesh
manojadinesh.blogspot.com

Thanks

I have a csv string in a text field that is unsorted and contains duplicates.
Is there a simple way to remove these and sort the string.

E.g
2,18,20,23,1,27,1,2,8,16,17,18,20,22,23,27

Do you need to eventually load the data in Postgres?

I'd personally use python to deal with this, we're talking 4 or 5 lines here,
if even. I suspect you can do the same with perl or ruby or whatever is your
weapon of choice.

How columns does your csv file has? Is it a one-line file?

--
http://yves.zioup.com
gpg: 4096R/32B0F416

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello,

I have a csv string in a text field that is unsorted and contains
duplicates.
Is there a simple way to remove these and sort the string.

E.g
2,18,20,23,1,27,1,2,8,16,17,18,20,22,23,27

i tried string to array and unique but that did not work...
Any suggestions on how to do this without writing a function?

Any help is appreciated.

Thanks
A

chris=# SELECT distinct x::int from
unnest(string_to_array('2,18,20,23,1,27,1,2,8,16,17,18,20,22,23,27',
',')) x order by x::int;

x
----
1
2
8
16
17
18
20
22
23
27
(10 rows)

Bye,
Chris.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Jul 27, 2015 at 12:57 PM, dinesh kumar <dineshkumar02@gmail.com>
wrote:

On Mon, Jul 27, 2015 at 12:53 PM, Alex Magnum <magnum11200@gmail.com>
wrote:

Hello,

I have a csv string in a text field that is unsorted and contains
duplicates.
Is there a simple way to remove these and sort the string.

E.g
2,18,20,23,1,27,1,2,8,16,17,18,20,22,23,27

i tried string to array and unique but that did not work...
Any suggestions on how to do this without writing a function?

Any help is appreciated.

Are you looking for this.

postgres=# SELECT unnest(string_to_array(t, ',')) from test group by 1;
unnest
--------
2
18
8
20
22
16
27
17
23
1
(10 rows)

OR

Might be something like this

postgres=# WITH sortedstring as
postgres-# (
postgres(# SELECT unnest(string_to_array(t, ','))::int from test group by 1
ORDER BY 1
postgres(# ) SELECT array_agg(unnest) FROM sortedstring;
array_agg
------------------------------
{1,2,8,16,17,18,20,22,23,27}
(1 row)

Regards,
Dinesh
manojadinesh.blogspot.com

Hi Danish, yes thats the one I was looking for. Thanks a lot!!!

On Tue, Jul 28, 2015 at 1:32 AM, dinesh kumar <dineshkumar02@gmail.com>
wrote: