17,088
社区成员
发帖
与我相关
我的任务
分享
select t.*
from RECORD_TABLE t where translate(substr(t.record_content,1,4),'#1234567890','#') is null--前四位是数字
and ((translate (substr(t.record_content,6,instr(t.record_content,'月')-6),'#1234567890','#')is null and length(substr(t.record_content,6,instr(t.record_content,'月')-6))>0--判断月份格式的合法性) or
substr(t.record_content,6,instr(t.record_content,'季度')-6) in ('一','二','三','四')--判断季度格式的合法性)
and substr(t.record_content,1,4)||lpad(decode(substr(t.record_content,6,instr(t.record_content,'月')-6)||substr(t.record_content,6,instr(t.record_content,'季度')-6),'一','01','二','04','三','07','四','10',substr(t.record_content,6,instr(t.record_content,'月')-6)||substr(t.record_content,6,instr(t.record_content,'季度')-6)),2,'0')>'200810'--取区间起始值
and substr(t.record_content,1,4)||lpad(decode(substr(t.record_content,6,instr(t.record_content,'月')-6)||substr(t.record_content,6,instr(t.record_content,'季度')-6),'一','03','二','06','三','09','四','12',substr(t.record_content,6,instr(t.record_content,'月')-6)||substr(t.record_content,6,instr(t.record_content,'季度')-6)),2,'0')<'201009'--取区间结束值
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as tds
SQL> select * from record_table;
RECORD_ID RECORD_CONTENT
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
7AGH3E48 2月份购买了一台电视机
46NS7CK6 三季度盖了一座新房子
167JKF06 2008年8月生了个胖宝宝
XJF83BG5 2009年12月又娶了个老婆
8DHN36SK 2010年一季度在家待岗
27FM389D 1995年5月
N8F86DN3 2012年会发生什么事情
7 rows selected
SQL> select t.*
2 from RECORD_TABLE t
3 where translate(substr(t.record_content, 1, 4), '#1234567890', '#') is null
4 and ((translate(substr(t.record_content,
5 6,
6 instr(t.record_content, '月') - 6),
7 '#1234567890',
8 '#') is null and
9 length(substr(t.record_content,
10 6,
11 instr(t.record_content, '月') - 6)) > 0) or
12 substr(t.record_content, 6, instr(t.record_content, '季度') - 6) in
13 ('一', '二', '三', '四'))
14 and substr(t.record_content, 1, 4) ||
15 lpad(decode(substr(t.record_content,
16 6,
17 instr(t.record_content, '月') - 6) ||
18 substr(t.record_content,
19 6,
20 instr(t.record_content, '季度') - 6),
21 '一',
22 '01',
23 '二',
24 '04',
25 '三',
26 '07',
27 '四',
28 '10',
29 substr(t.record_content,
30 6,
31 instr(t.record_content, '月') - 6) ||
32 substr(t.record_content,
33 6,
34 instr(t.record_content, '季度') - 6)),
35 2,
36 '0') > '200810'
37 and substr(t.record_content, 1, 4) ||
38 lpad(decode(substr(t.record_content,
39 6,
40 instr(t.record_content, '月') - 6) ||
41 substr(t.record_content,
42 6,
43 instr(t.record_content, '季度') - 6),
44 '一',
45 '03',
46 '二',
47 '06',
48 '三',
49 '09',
50 '四',
51 '12',
52 substr(t.record_content,
53 6,
54 instr(t.record_content, '月') - 6) ||
55 substr(t.record_content,
56 6,
57 instr(t.record_content, '季度') - 6)),
58 2,
59 '0') < '201009';
RECORD_ID RECORD_CONTENT
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
XJF83BG5 2009年12月又娶了个老婆
8DHN36SK 2010年一季度在家待岗
SQL>