一条查询语句,是比较版本号的,例如:
select * from table1 where ver_no > '3.0.0.8';
由于Oracle字符串比较机制导致数据库中存在3.0.0.36的记录查询不出来,想得到'3.0.0.36' > '3.0.0.8'的效果,大家有没有什么简单办法可以解决,非常感谢!
select * from table1 where ver_no > '3.0.0.8';
由于Oracle字符串比较机制导致数据库中存在3.0.0.36的记录查询不出来,想得到'3.0.0.36' > '3.0.0.8'的效果,大家有没有什么简单办法可以解决,非常感谢!
with table1(ver_no) as(
select '3.0.0.8' from dual
union all select '3.0.0.36' from dual
union all select '2.0.0.43' from dual
)
select * from table1 where to_number(regexp_substr(ver_no,'[0-9]+'))>=to_number(regexp_substr('3.0.0.8','[0-9]+'))
and to_number(regexp_substr(ver_no,'[0-9]+$'))>to_number(regexp_substr('3.0.0.8','[0-9]+$'));
/*
VER_NO
----------------
3.0.0.36
*/
这样写是不是就对了?zhangandliselect * from C_VERSION_RELEASED where
to_number(regexp_substr(VERSION_NUMBER,'[0-9]+')) >= to_number(regexp_substr('3.0.0.8','[0-9]+'))
and
to_number(regexp_substr(VERSION_NUMBER,'[0-9]+',1,2)) >= to_number(regexp_substr('3.0.0.8','[0-9]+',1,2))
and
to_number(regexp_substr(VERSION_NUMBER,'[0-9]+',1,3)) >= to_number(regexp_substr('3.0.0.8','[0-9]+',1,3))
and
to_number(regexp_substr(VERSION_NUMBER,'[0-9]+$')) > to_number(regexp_substr('3.0.0.8','[0-9]+$'));