Is there a postgresql function, preferably native function, that can sort a string such as 'banana' to 'aaabnn'?
Algorithmic efficiency of sorting is not of much importance since words will never be too long. However, database join efficiency is of some but not critical importance.
There is no native function with such functionality but you can use regexp_split_to_table to do so as this:
select theword
from (select regexp_split_to_table('banana',E'(?=.)') theword) tab
order by theword;
The result will be:
theword
a
a
a
b
n
n
This (?=.) will split by each character leaving the character as separator. It will also identify spaces. If you have a word with spaces and do not want it (the space) use E'(\\s*)' matches any whitespace character. I don't recall what the E means. I will search and edit the answer asap.
As explained in the DOCs in the section "regexp_split_to_table"
EDIT: As I said: The meaning of the E before the string you can see here: What's the "E" before a Postgres string?
select array_to_string(array(select unnest(string_to_array('banana', null)) order by 1), '') as r;. The combination of string_to_array with unnest is more orthogonal, and the array and array_to_string are icing on the cake returning me the sorted string itself.
select array_to_string(array(select unnest(string_to_array('banana', null)) order by 1), '') as r;