postgresql add tag
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;        
```

  

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.