Search Postgresql Archives

Re: Help function to sort string

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Le mardi 15 octobre 2013 à 08:52 -0700, ginkgo36 a écrit :

> 1. I want to sort string follow anphabet and I used this query:
> select string_agg(x, ';') from (select
> trim(unnest(regexp_split_to_array('ECD FORM; BODY; PREDILUTED; CHROMO-GENIC;
> AUTO;RABBIT; FORMAT',';'))) x order by x) a;
> 
> -- result: AUTO; BODY; CHROMOGENIC; ECD FORM;  FORMAT; PREDILUTED;  RABBIT
> -->I expected this rusult
> 
> In my database I have a column with alot of rows data. I want that query
> become a function to more easy to using. But I can not write a function :(.
> please hepl me.
> For example, I have column "data_text" with data like this:
> Row 1: AUTO; BODY; PREDILUTED; ECD FORM; RABBIT; FORMAT; CHROMOGENIC
> Row 2: ECD FORM; BODY; PREDILUTED; CHROMO-GENIC; AUTO; RABBIT; FORMAT
> Row 3: FORMAT; ECD FORM; AUTO
> Row 3: ANHYDROUS; DENATURED; PREDILUTED; CHROMOGENIC
> 
> When I run funtion, the result:
> Row 1: AUTO; BODY; CHROMOGENIC; ECD.FORM; FORMAT; PREDILUTED; RABBIT
> Row 2: AUTO; BODY; CHROMO-GENIC; ECD FORM; FORMAT; PREDILUTED; RABBIT
> Row 3: AUTO; ECD FORM; FORMAT
> Row 4: ANHYDROUS; CHROMOGENIC; DENATURED; PREDILUTED

This works :

CREATE TABLE foo (id serial, data_text text);

INSERT INTO foo (data_text) values ('AUTO; BODY; PREDILUTED; ECD FORM;
RABBIT; FORMAT; CHROMOGENIC'), ('ECD FORM; BODY; PREDILUTED;
CHROMO-GENIC; AUTO; RABBIT; FORMAT'), ('FORMAT; ECD FORM; AUTO');

WITH t1 AS (SELECT id, unnest(string_to_array(data_text, '; ')) FROM foo
ORDER BY 1, 2),
t2 AS (SELECT id, array_agg(unnest) over (PARTITION BY id) AS reordered
FROM t1) 
SELECT t2.id, array_to_string(t2.reordered, '; ') FROM t2 GROUP BY id,
reordered;


Result :

 id |                        array_to_string                         
----+----------------------------------------------------------------
  1 | AUTO; BODY; CHROMOGENIC; ECD FORM; FORMAT; PREDILUTED; RABBIT
  2 | AUTO; BODY; CHROMO-GENIC; ECD FORM; FORMAT; PREDILUTED; RABBIT
  3 | AUTO; ECD FORM; FORMAT


-- 
                                        Salutations, Vincent Veyron

http://marica.fr/site/demonstration
Gestion des contentieux juridiques, des contrats et des sinistres d'assurance



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux