1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
drop table if exists t1;
set names latin1;
drop table if exists t1;
create table t1 as
select repeat(' ', 64) as s1, repeat(' ',64) as s2
union
select null, null;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`s1` varchar(64) DEFAULT NULL,
`s2` varchar(64) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
delete from t1;
insert into t1 values('aaa','aaa');
insert into t1 values('aaa|qqq','qqq');
insert into t1 values('gheis','^[^a-dXYZ]+$');
insert into t1 values('aab','^aa?b');
insert into t1 values('Baaan','^Ba*n');
insert into t1 values('aaa','qqq|aaa');
insert into t1 values('qqq','qqq|aaa');
insert into t1 values('bbb','qqq|aaa');
insert into t1 values('bbb','qqq');
insert into t1 values('aaa','aba');
insert into t1 values(null,'abc');
insert into t1 values('def',null);
insert into t1 values(null,null);
insert into t1 values('ghi','ghi[');
select HIGH_PRIORITY s1 regexp s2 from t1;
s1 regexp s2
0
0
0
1
1
1
1
1
1
1
NULL
NULL
NULL
NULL
drop table t1;
create table t1 (xxx char(128));
insert into t1 (xxx) values('this is a test of some long text to see what happens');
select * from t1 where xxx regexp('is a test of some long text to');
xxx
this is a test of some long text to see what happens
explain extended select * from t1 where xxx regexp('is a test of some long text to');
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
Note 1003 select 'this is a test of some long text to see what happens' AS `xxx` from dual where 1
select * from t1 where xxx regexp('is a test of some long text to ');
xxx
this is a test of some long text to see what happens
select * from t1 where xxx regexp('is a test of some long text to s');
xxx
this is a test of some long text to see what happens
select * from t1 where xxx regexp('is a test of some long text to se');
xxx
this is a test of some long text to see what happens
drop table t1;
create table t1 (xxx char(128));
insert into t1 (xxx) values('this is some text: to test - out.reg exp (22/45)');
select * from t1 where xxx REGEXP '^this is some text: to test - out\\.reg exp [[(][0-9]+[/\\][0-9]+[])][ ]*$';
xxx
this is some text: to test - out.reg exp (22/45)
drop table t1;
select _latin1 0xFF regexp _latin1 '[[:lower:]]' COLLATE latin1_bin;
_latin1 0xFF regexp _latin1 '[[:lower:]]' COLLATE latin1_bin
1
select _koi8r 0xFF regexp _koi8r '[[:lower:]]' COLLATE koi8r_bin;
_koi8r 0xFF regexp _koi8r '[[:lower:]]' COLLATE koi8r_bin
0
select _latin1 0xFF regexp _latin1 '[[:upper:]]' COLLATE latin1_bin;
_latin1 0xFF regexp _latin1 '[[:upper:]]' COLLATE latin1_bin
0
select _koi8r 0xFF regexp _koi8r '[[:upper:]]' COLLATE koi8r_bin;
_koi8r 0xFF regexp _koi8r '[[:upper:]]' COLLATE koi8r_bin
1
select _latin1 0xF7 regexp _latin1 '[[:alpha:]]';
_latin1 0xF7 regexp _latin1 '[[:alpha:]]'
0
select _koi8r 0xF7 regexp _koi8r '[[:alpha:]]';
_koi8r 0xF7 regexp _koi8r '[[:alpha:]]'
1
select _latin1'a' regexp _latin1'A' collate latin1_general_ci;
_latin1'a' regexp _latin1'A' collate latin1_general_ci
1
select _latin1'a' regexp _latin1'A' collate latin1_bin;
_latin1'a' regexp _latin1'A' collate latin1_bin
0
create table t1 (a varchar(40));
insert into t1 values ('C1'),('C2'),('R1'),('C3'),('R2'),('R3');
prepare stmt1 from 'select a from t1 where a rlike ? order by a';
set @a="^C.*";
execute stmt1 using @a;
a
C1
C2
C3
set @a="^R.*";
execute stmt1 using @a;
a
R1
R2
R3
deallocate prepare stmt1;
drop table t1;
End of 4.1 tests
SELECT 1 REGEXP NULL;
1 REGEXP NULL
NULL
SELECT '' REGEXP BINARY NULL;
'' REGEXP BINARY NULL
NULL
SELECT NULL REGEXP BINARY NULL;
NULL REGEXP BINARY NULL
NULL
SELECT 'A' REGEXP BINARY NULL;
'A' REGEXP BINARY NULL
NULL
SELECT "ABC" REGEXP BINARY NULL;
"ABC" REGEXP BINARY NULL
NULL
End of 5.0 tests
CREATE TABLE t1(a INT, b CHAR(4));
INSERT INTO t1 VALUES (1, '6.1'), (1, '7.0'), (1, '8.0');
PREPARE stmt1 FROM "SELECT a FROM t1 WHERE a=1 AND '7.0' REGEXP b LIMIT 1";
EXECUTE stmt1;
a
1
EXECUTE stmt1;
a
1
EXECUTE stmt1;
a
1
EXECUTE stmt1;
a
1
DEALLOCATE PREPARE stmt1;
DROP TABLE t1;
End of 5.1 tests
SELECT ' ' REGEXP '[[:blank:]]';
' ' REGEXP '[[:blank:]]'
1
SELECT '\t' REGEXP '[[:blank:]]';
'\t' REGEXP '[[:blank:]]'
1
SELECT ' ' REGEXP '[[:space:]]';
' ' REGEXP '[[:space:]]'
1
SELECT '\t' REGEXP '[[:space:]]';
'\t' REGEXP '[[:space:]]'
1
#
# MDEV-13967 Parameter data type control for Item_long_func
#
SELECT REGEXP_INSTR('111222333',2);
REGEXP_INSTR('111222333',2)
4
|
