oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
#### owner: jim.wjh
5
#### owner group: sql3
6
#### description: 测试instr函数
7
# tags: expr, datatype
8

9
--enable_sorted_result
10
--result_format 4
11

12
--echo ================ expression instr ================
13

14
select instr('abc', '') from dual;
15
select instr('', '') from dual;
16
select instr('', 'abc') from dual;
17
select instr('abc', 'abcd') from dual;
18
select instr('abc', 'abc') from dual;
19
select instr('abc', 'a') from dual;
20
select instr('abc', 'b') from dual;
21
select instr('abc', 'c') from dual;
22
select instr('abc', 'bc') from dual;
23
select instr('abcbc', 'bc') from dual;
24
select instr('阿里巴巴', '阿里') from dual;
25
select instr('阿里巴巴', '巴巴') from dual;
26
select instr('阿里巴巴巴巴', '巴巴') from dual;
27
select instr('阿里巴巴', '阿里巴巴') from dual;
28
select instr(123, '23') from dual;
29
select instr('123', 123) from dual;
30
select instr('123.400000', 23.4) from dual;
31
select instr('123.400000', 123.4) from dual;
32
select instr('123.400000', null) from dual;
33
select instr(null, '巴巴') from dual;
34
select instr('巴巴', null) from dual;
35
select instr(null, null) from dual;
36
select instr('123', true) from dual;
37

38
select instr(123, 23) from dual;
39
select instr(123, 23456) from dual;
40
select instr(123.4, 123) from dual;
41
select instr(1234, 123.4) from dual;
42
select instr(1234, null) from dual;
43
select instr(null, 123) from dual;
44

45
select instr(123.400000, 123.4) from dual;
46
select instr(123.400000, 123.41) from dual;
47
select instr(123.400000, null) from dual;
48
select instr(null, 123.41) from dual;
49

50
--disable_warnings
51
drop table if exists test;
52
--enable_warnings
53
create table test(c1 date primary key);
54
sleep 5;
55
#alter session set nls_date_format = "yyyy-mm-dd hh24:mi:ss";
56
insert into test values('2015-5-5');
57
select instr(c1, '201') from test;
58
select instr(c1, '') from test;
59
select instr(c1, 'haha') from test;
60
select instr(c1, '-5') from test;
61
select instr(c1, '2015-5-5') from test;
62
select instr(c1, 201) from test;
63
select instr(c1, 201.1) from test;
64
select instr(c1, null) from test;
65
select instr(null, c1) from test;
66

67

68
# with more params
69
--error 1582
70
select instr() from dual;
71
--error 1582
72
select instr('abcda') from dual;
73
--error 1582
74
select instr('abcda', 'a', 1) from dual;
75
--error 1582
76
select instr('abcda', 'a', 1, 1) from dual;
77

78
--disable_warnings
79
drop table test;
80
--enable_warnings
81

82