1

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.

user2297550
  • 3,142
  • 3
  • 28
  • 39

1 Answers1

3

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?

Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
  • Hmm... I got inspired to do this instead: `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. – user2297550 Jun 01 '17 at 07:58
  • Although it works I doubt that your solution will be more performatic then the one I provide. Compare then doing an execution plan of both. – Jorge Campos Jun 01 '17 at 12:34