3,491
社区成员
发帖
与我相关
我的任务
分享
SQL>
SQL> create table test(sr varchar(10), rq date default sysdate);
Table created
SQL> begin
2 insert into test(sr) values('5岁3月');
3 insert into test(sr) values('8岁8月');
4 insert into test(sr) values('8岁10月');
5 insert into test(sr) values('43岁');
6 end;
7 /
PL/SQL procedure successfully completed
SQL> with m as (
2 select
3 regexp_substr(replace(sr,'月',''),'[^岁]+', 1, 1) sr_y,
4 regexp_substr(replace(sr,'月',''),'[^岁]+', 1, 2) sr_m,
5 sr,
6 rq
7 from test
8 )
9 select sr, rq, add_months(rq, -(sr_y * 12 + nvl(sr_m,0))) rq_new from m;
SR RQ RQ_NEW
---------- ----------- -----------
5岁3月 2018-05-30 2013-02-28
8岁8月 2018-05-30 2009-09-30
8岁10月 2018-05-30 2009-07-30
43岁 2018-05-30 1975-05-30
SQL> drop table test purge;
Table dropped
SQL>