PeterVandivier
I would like to:
* split a string by parts
* reverse the order of the parts
* reassemble the parts in reverse order
By way of example...
<>https://dbfiddle.uk/?rdbms=postgres_13&fiddle=6ea4a45fda79a10ab1e1be28703048ed&hide=12
From experience with a similar situation on another platform, I don't expect that the above implementation will rigorously preserve ordering. [^1]^,^[^2]
1. Is this assumption correct?
2. Is there a better implementation of by-parts-reversal that _does_ rigorously preserve ordering?
---
[^1]: https://dba.stackexchange.com/questions/207274/string-split-and-ordered-results
[^2]: https://feedback.azure.com/forums/908035-sql-server/suggestions/32902852-string-split-is-not-feature-complete
Top Answer
Jack Douglas
If you want to guarantee ordering (and you should), you can use [with ordinality](https://www.postgresql.org/docs/12/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS) since version 9.4, along with an aggregate with [order by](https://www.postgresql.org/docs/12/sql-expressions.html#SYNTAX-AGGREGATES):
<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=e07bf534d91b015276b4e093ffcee18c&hide=1
> From experience with a similar situation on another platform, I don't expect that the above implementation will rigorously preserve ordering.
>
> 1. Is this assumption correct?
At the very least, given that the ordering isn't otherwise guaranteed, you run the risk that a future version of postgres will return the results in a different order.
You will probably find edge-cases where the ordering breaks down (parallel queries for example?) even on current versions. I guess you have to ask if "[usually works](https://www.postgresql.org/docs/12/functions-aggregate.html)" is good enough for you:
> This ordering is unspecified by default, but can be controlled by writing an `ORDER BY` clause within the aggregate call, as shown in Section 4.2.7. Alternatively, supplying the input values from a sorted subquery will ***usually work*** …
(my emphasis)
Answer #2
Truilus
I would probably use a scalar sub-select:
```
select id,
(select string_agg(e, '.' order by idx desc)
from unnest(string_to_array(bar, '.')) with ordinality as t(e,idx)) as bar
from foo;
```
or maybe create a function to make this easier:
```
create or replace function reverse_elements(p_list text, p_delimiter text default '.')
returns text
as
$$
select string_agg(e, p_delimiter order by idx desc)
from unnest(string_to_array(p_list, p_delimiter)) with ordinality as t(e,idx);
$$
language sql
immutable
;
```
Then it's as simple as:
```
select id,
reverse_elements(bar)
from foo;
```