Re: Help function to sort string

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