SQL>
SQL> create table test(name nvarchar2(30));
Table created
SQL> begin
2 insert into test values('11');
3 insert into test values('1');
4 insert into test values('2');
5 insert into test values('12');
6 insert into test values('test1:1; test2:2');
7 insert into test values('aaa:2; bbb:3');
8 end;
9 /
PL/SQL procedure successfully completed
SQL> select name from test
2 order by regexp_substr(name,'^\d+',1) +0 nulls last, name;
NAME
-------------------------------------------------------------
1
2
11
12
aaa:2; bbb:3
test1:1; test2:2
6 rows selected
SQL> drop table test purge;
Table dropped
SQL>
#4
如果其中一个字段是IP
172.16.11.250
172.16.11.251
想对IP倒序排列
order by regexp_substr(ip,'^\d+',1) +0 desc nulls last, ip
SQL>
SQL> create table test(ip varchar(20));
Table created
SQL> begin
2 insert into test values('172.116.11.250');
3 insert into test values('171.116.111.250');
4 insert into test values('171.162.211.250');
5 insert into test values('172.162.211.250');
6 insert into test values('172.62.211.250');
7 end;
8 /
PL/SQL procedure successfully completed
SQL> select * from test
2 order by regexp_substr(ip,'[^.]+',1,1),regexp_substr(ip,'[^.]+',1,2);
IP
--------------------
171.116.111.250
171.162.211.250
172.116.11.250
172.162.211.250
172.62.211.250
SQL> drop table test purge ;
Table dropped
SQL>
#8
SQL>
SQL> create table test(ip varchar(20));
Table created
SQL> begin
2 insert into test values('172.116.11.250');
3 insert into test values('171.116.111.250');
4 insert into test values('171.162.211.250');
5 insert into test values('172.162.211.250');
6 insert into test values('172.62.211.250');
7 end;
8 /
PL/SQL procedure successfully completed
SQL> select * from test
2 order by regexp_substr(ip,'[^.]+',1,1),regexp_substr(ip,'[^.]+',1,2);
IP
--------------------
171.116.111.250
171.162.211.250
172.116.11.250
172.162.211.250
172.62.211.250
SQL> drop table test purge ;
Table dropped
SQL>
SQL> create table test(name nvarchar2(30));
Table created
SQL> begin
2 insert into test values('11');
3 insert into test values('1');
4 insert into test values('2');
5 insert into test values('12');
6 insert into test values('test1:1; test2:2');
7 insert into test values('aaa:2; bbb:3');
8 end;
9 /
PL/SQL procedure successfully completed
SQL> select name from test
2 order by regexp_substr(name,'^\d+',1) +0 nulls last, name;
NAME
-------------------------------------------------------------
1
2
11
12
aaa:2; bbb:3
test1:1; test2:2
6 rows selected
SQL> drop table test purge;
Table dropped
SQL>
#4
-- 目前,你这几行数据,可以这样写,你跑跑看
SQL>
SQL> create table test(name nvarchar2(30));
Table created
SQL> begin
2 insert into test values('11');
3 insert into test values('1');
4 insert into test values('2');
5 insert into test values('12');
6 insert into test values('test1:1; test2:2');
7 insert into test values('aaa:2; bbb:3');
8 end;
9 /
PL/SQL procedure successfully completed
SQL> select name from test
2 order by regexp_substr(name,'^\d+',1) +0 nulls last, name;
NAME
-------------------------------------------------------------
1
2
11
12
aaa:2; bbb:3
test1:1; test2:2
6 rows selected
SQL> drop table test purge;
Table dropped
SQL>
如果其中一个字段是IP
172.16.11.250
172.16.11.251
想对IP倒序排列
order by regexp_substr(ip,'^\d+',1) +0 desc nulls last, ip
这个会有问题,是正则表达式的问题吗,该怎么写呢
#5
如果其中一个字段是IP
172.16.11.250
172.16.11.251
想对IP倒序排列
order by regexp_substr(ip,'^\d+',1) +0 desc nulls last, ip
SQL>
SQL> create table test(ip varchar(20));
Table created
SQL> begin
2 insert into test values('172.116.11.250');
3 insert into test values('171.116.111.250');
4 insert into test values('171.162.211.250');
5 insert into test values('172.162.211.250');
6 insert into test values('172.62.211.250');
7 end;
8 /
PL/SQL procedure successfully completed
SQL> select * from test
2 order by regexp_substr(ip,'[^.]+',1,1),regexp_substr(ip,'[^.]+',1,2);
IP
--------------------
171.116.111.250
171.162.211.250
172.116.11.250
172.162.211.250
172.62.211.250
SQL> drop table test purge ;
Table dropped
SQL>
#8
SQL>
SQL> create table test(ip varchar(20));
Table created
SQL> begin
2 insert into test values('172.116.11.250');
3 insert into test values('171.116.111.250');
4 insert into test values('171.162.211.250');
5 insert into test values('172.162.211.250');
6 insert into test values('172.62.211.250');
7 end;
8 /
PL/SQL procedure successfully completed
SQL> select * from test
2 order by regexp_substr(ip,'[^.]+',1,1),regexp_substr(ip,'[^.]+',1,2);
IP
--------------------
171.116.111.250
171.162.211.250
172.116.11.250
172.162.211.250
172.62.211.250
SQL> drop table test purge ;
Table dropped