例如
字段:num
数据如下:
AC 20-74
AC 20-44
AC 20-37E
AC 20-43C
AC 20-149
AC 20-109A
AC 20-157希望排序后得到的结果只按数字大小排序,即
AC 20-37E
AC 20-43C
AC 20-44
AC 20-74
AC 20-109A
AC 20-149
AC 20-157我试过用substr(num,*,*)来排,但是就是排不出来,请问各位大侠,sql语句该如何写?
字段:num
数据如下:
AC 20-74
AC 20-44
AC 20-37E
AC 20-43C
AC 20-149
AC 20-109A
AC 20-157希望排序后得到的结果只按数字大小排序,即
AC 20-37E
AC 20-43C
AC 20-44
AC 20-74
AC 20-109A
AC 20-149
AC 20-157我试过用substr(num,*,*)来排,但是就是排不出来,请问各位大侠,sql语句该如何写?
[SYS@orcl] SQL>WITH t1 AS(
2 SELECT 'AC' A, '20-74' B
3 FROM DUAL
4 UNION ALL
5 SELECT 'AC' A, '20-44' B
6 FROM DUAL
7 UNION ALL
8 SELECT 'AC' A, '20-37E' B
9 FROM DUAL
10 UNION ALL
11 SELECT 'AC' A, '20-43C' B
12 FROM DUAL
13 UNION ALL
14 SELECT 'AC' A, '20-149' B
15 FROM DUAL
16 UNION ALL
17 SELECT 'AC' A, '20-109A' B
18 FROM DUAL
19 UNION ALL
20 SELECT 'AC' A, '20-157' B FROM DUAL
21 )SELECT *
22 FROM t1
23 ORDER BY A,TO_NUMBER(regexp_replace(B,'([0-9]+)-([0-9]+)|([a-z|A-Z]+)','\1\2'));A B
-- -------
AC 20-37E
AC 20-43C
AC 20-44
AC 20-74
AC 20-109A
AC 20-149
AC 20-157已选择7行。