我正在尝试按降序排列字符串字段。
排序规则：
“p”和“s”将始终保持不变
空值应该是最后一个
整个字符需要按降序排序，第一个优先顺序是“p”之前的字符，然后是“p”之后的字符，最后是s之后的字符。
p之前的字符应该像十进制数字一样降序排列，最高的第一个字符是-4.5,4.2,3.9，它们决定了主顺序。
如果“p”之前的字符相同，那么它应该使用“p”和“s”之间的字符作为第二个优先顺序，并按数字排序，最高优先。
如果“p”前后的字符相同，则“s”后的字符应考虑作为数字排序，首先是最高的。
样本源数据：

3.9P2S1
4.0P5S1
3.10P4S1
3.11P2S3
3.7P2S1
3.2P10S1
4.0P4S1
3.5P2S1
4.0P16S1
3.12P6S1
3.12P10S2
3.14P3S2

预期产量：

4.0P16S1
4.0P5S1
4.0P4S1
3.14P3S2
3.12P10S2
3.12P6S1
3.11P2S3
3.10P4S1
3.9P2S1
3.7P2S1
3.5P2S1
3.2P10S1

这是我到目前为止尝试过的，但是我不能得到想要的结果。

with firmware_name as (
select '3.9P2S1' as firmware from dual union all
select '4.0P5S1' as firmware from dual union all
select '3.10P4S1' as firmware from dual union all
select '3.11P2S3' as firmware from dual union all
select '3.7P2S1' as firmware from dual union all
select '3.2P10S1' as firmware from dual union all
select '4.0P4S1' as firmware from dual union all
select '3.5P2S1' as firmware from dual union all
select '4.0P16S1' as firmware from dual union all
select '3.12P6S1' as firmware from dual union all
select '3.12P10S2' as firmware from dual union all
select '3.14P3S2' as firmware from dual)
select * from firmware_name
order by to_number(regexp_substr(firmware, '^\d+')) desc nulls last,
to_number(regexp_substr(firmware, '^\d+\.(\d+)', 1, 1, null, 1)) desc,
regexp_replace(firmware, '\d+\.\d+') desc;

根据上面的“4.0p5s1”是最高的，这显然是错误的。我无法整理最后一部分。任何帮助都会很棒。
谢谢