[GENERAL] Alphanumeric natural order sorting : need generic solution

[Umashanker, Srividhya]

All,

I am looking for a generic solution to get the Alphanumeric sorting.


*         The user can request for any field to be sorted.

*         There is no specific format the alphanumeric string  can follow. So 
that we can split using regex and split and sorted.

*         Need an efficient way. Should not degrade performance.

I tried all possible collation  example -- select * from test order by name 
collate "en_US";

Is there someone who has solved this problem with writing a function?

Below are more details -


Database
[cid:image001.png@01CE26FE.97F75300]

Table
[cid:image002.png@01CE26FE.97F75300]

Rows with orderby
[cid:image003.png@01CE26FE.97F75300]

I am expecting


1, bay1
2, bay2
10, bay10
11, bay11

-vidhya

<<inline: image001.png>>

<<inline: image002.png>>

<<inline: image003.png>>