Re: [GENERAL] Sorting CSV string and removing Duplicates

2015-07-27 Thread Yves Dorfsman 
 
 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

Re: [GENERAL] Sorting CSV string and removing Duplicates

2015-07-27 Thread dinesh kumar 
On Mon, Jul 27, 2015 at 12:57 PM, dinesh kumar dineshkuma...@gmail.com
wrote:

 On Mon, Jul 27, 2015 at 12:53 PM, Alex Magnum magnum11...@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



 Regards,
 Dinesh
 manojadinesh.blogspot.com

 Thanks
 A

Re: [GENERAL] Sorting CSV string and removing Duplicates

2015-07-27 Thread Chris Mair 
 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

Re: [GENERAL] Sorting CSV string and removing Duplicates

2015-07-27 Thread dinesh kumar 
On Mon, Jul 27, 2015 at 12:53 PM, Alex Magnum magnum11...@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
 A

[GENERAL] Sorting CSV string and removing Duplicates

2015-07-27 Thread Alex Magnum 
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

Re: [GENERAL] Sorting CSV string and removing Duplicates

2015-07-27 Thread Alex Magnum 
Hi Danish, yes thats the one I was looking for. Thanks a lot!!!


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



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

 On Mon, Jul 27, 2015 at 12:53 PM, Alex Magnum magnum11...@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



 Regards,
 Dinesh
 manojadinesh.blogspot.com

 Thanks
 A