oracle如何排序 数字和字母组合的字符串
0
悬赏园豆：5
[已解决问题]
解决于 2023-06-02 10:10
需求排序成1N9后面是1N10 这样以此类推
顺序就是1N后面一直到100这样排，2N后面一到100这样排
大神们这个用Oracle 排序 怎么排呀
原sql排序效果
select TOWER_NO,to_number(REGEXP_REPLACE(TOWER_NO,'[^0-9]','')) from cis_PM_Plan_Tower where bid_Project_No = '13012020000200401' ORDER BY to_number(REGEXP_REPLACE(TOWER_NO,'[^0-9]',''))
1WSMG
2WSMG
1N1
1N2
1N3
1N4
1N5
1N6
1N7
1N8
1N9
2N1
2N2
2N3
2N4
2N5
2N6
2N7
2N8
2N9
1N10
1N11
1N12
1N13
1N14
1N15
1N16
1N17
1N18
1N19
1N20
1N21
1N22
1N23
1N24
1N25
1N26
1N27
1N28
1N29
1N30
1N31
1N32
1N33
1N34
1N35
1N36
1N37
1N38
1N39
1N40
1N41
1N42
1N43
1N44
1N45
1N46
1N47
1N48
1N49
1N50
1N51
1N52
1N53
1N54
1N55
1N56
1N57
1N58
1N59
1N60
1N61
1N62
1N63
1N64
1N65
1N66
1N67
1N68
1N69
1N70
1N71
1N72
1N73
1N74
1N75
1N76
1N77
1N78
1N79
1N80
1N81
1N82
1N83
1N84
1N85
1N86
1N87
1N88
1N89
1N90
1N91
1N92
1N93
1N94
1N95
1N96
1N97
1N98
1N99
2N10
2N11
2N12
2N13
2N14
2N15
2N16
2N17
2N18
2N19
2N20
2N21
2N22
2N23
2N24
2N25
2N26
2N27
2N28
2N29
2N30
2N31
2N32
2N33
2N34
2N35
2N36
2N37
2N38
2N39
2N40
2N41
2N42
2N43
2N44
2N45
2N46
2N47
2N48
2N49
2N50
2N51
2N52
2N53
2N54
2N55
2N56
2N57
2N58
2N59
2N60
2N61
2N62
2N63
2N64
2N65
2N66
2N67
2N68
2N69
2N70
2N71
2N72
2N73
2N74
2N75
2N76
2N77
2N78
2N79
2N80
2N81
2N82
2N83
2N84
2N85
2N86
2N87
2N88
2N89
2N90
2N91
2N92
2N93
2N94
2N95
2N96
2N97
2N98
2N99
1N100
1N101
1N102
1N103
1N104
1N105
1N106
1N107
1N108
1N109
1N110
1N111
1N112
1N113
1N114
1N115
1N116
1N117
1N118
1N119
1N120
1N121
1N122
1N123
2N100
2N101
2N102
2N103
2N104
2N105
2N106
2N107
2N108
2N109
2N110
2N111
2N112
2N113
2N114
2N115
2N116
2N117
2N118
2N119
2N120
2N121
2N122
2N123
2N124
2N125
最佳答案
0
要按照你描述的需求进行排序，可以使用Oracle的排序功能来实现。你可以使用ORDER BY子句，结合REGEXP_SUBSTR函数对"TOWER_NO"列进行排序。
以下是一个示例查询，按照你描述的需求进行排序：
sql
Copy code
SELECT TOWER_NO
FROM cis_PM_Plan_Tower
WHERE bid_Project_No = '13012020000200401'
ORDER BY to_number(REGEXP_SUBSTR(TOWER_NO, '\d+')), TOWER_NO;
这个查询使用REGEXP_SUBSTR函数提取"TOWER_NO"列中的数字部分，并将其转换为数字进行排序。然后，它按照数字排序和原始字符串进行排序，以确保数字部分的顺序正确。
请注意，这个查询假设所有的"TOWER_NO"都遵循相同的模式，并且数字部分始终位于字符串的开头。如果有其他情况或模式，请提供更多的详细信息，以便我能够提供更准确的解决方案。
收获园豆：5
看了您的脚本,和理想排序还是有差距, 理想排序1N1排序后面应当是1N2,然后在以此类推,1N9后面应当是1N10,1N99后面应当是1N100 优先排完1开头,在排2开头.
理想排序:
1N1 1N2 1N3 1N4 1N5 1N6 1N7 1N8 1N9 1N10 1N11 1N12 1N13 1N14 1N15 1N16 1N17 1N18 1N19 1N20 1N21 1N22 1N23 1N24 1N25 1N26 1N27 1N28 1N29 1N30 1N31 1N32 1N33 1N34 1N35 1N36 1N37 1N38 1N39 1N40 1N41 1N42 1N43 1N44 1N45 1N46 1N47 1N48 1N49 1N50 1N51 1N52 1N53 1N54 1N55 1N56 1N57 1N58 1N59 1N60 1N61 1N62 1N63 1N64 1N65 1N66 1N67 1N68 1N69 1N70 1N71 1N72 1N73 1N74 1N75 1N76 1N77 1N78 1N79 1N80 1N81 1N82 1N83 1N84 1N85 1N86 1N87 1N88 1N89 1N90 1N91 1N92 1N93 1N94 1N95 1N96 1N97 1N98 1N99 1N100 1N101 1N102 1N103 1N104 1N105 1N106 1N107 1N108 1N109 1N110 1N111 1N112 1N113 1N114 1N115 1N116 1N117 1N118 1N119 1N120 1N121 1N122 1N123 2N1 2N2 2N3 2N4 2N5 2N6 2N7 2N8 2N9 2N10 2N11 2N12 2N13 2N14 2N15 2N16 2N17 2N18 2N19 2N20 2N21 2N22 2N23 2N24 2N25 2N26 2N27 2N28 2N29 2N30 2N31 2N32 2N33 2N34 2N35 2N36 2N37 2N38 2N39 2N40 2N41 2N42 2N43 2N44 2N45 2N46 2N47 2N48 2N49 2N50 2N51 2N52 2N53 2N54 2N55 2N56 2N57 2N58 2N59 2N60 2N61 2N62 2N63 2N64 2N65 2N66 2N67 2N68 2N69 2N70 2N71 2N72 2N73 2N74 2N75 2N76 2N77 2N78 2N79 2N80 2N81 2N82 2N83 2N84 2N85 2N86 2N87 2N88 2N89 2N90 2N91 2N92 2N93 2N94 2N95 2N96 2N97 2N98 2N99 2N100 2N101 2N102 2N103 2N104 2N105 2N106 2N107 2N108 2N109 2N110 2N111 2N112 2N113 2N114 2N115 2N116 2N117 2N118 2N119 2N120 2N121 2N122 2N123 2N124 2N125
