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) CHARACTER SET latin1 DEFAULT NULL,
  `s2` varchar(64) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=default_engine DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_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);
select HIGH_PRIORITY s1 regexp s2 from t1;
s1 regexp s2
1
1
1
1
1
1
1
0
0
0
NULL
NULL
NULL
SELECT 'ghi' REGEXP 'ghi[';
ERROR HY000: The regular expression contains an unclosed bracket expression.
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');
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
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 select * from t1 where xxx regexp('is a test of some long text to');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`xxx` AS `xxx` from `test`.`t1` where regexp_like(`test`.`t1`.`xxx`,'is a test of some long text to')
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]+[])][ ]*$';
ERROR HY000: The regular expression contains an unclosed bracket expression.
drop table t1;
# The Henry Spencer library used prior to ICU was inonsistent
# here. When the regular expression search is performed in a
# case-insensitive manner, both '[[:lower:]]' and '[[:upper:]]'
# will match.
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;
ERROR HY000: Character set 'latin1_swedish_ci' cannot be used in conjunction with 'binary' in call to regexp_like.
SELECT NULL REGEXP BINARY NULL;
NULL REGEXP BINARY NULL
NULL
Warnings:
Warning	1287	'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead
SELECT 'A' REGEXP BINARY NULL;
ERROR HY000: Character set 'latin1_swedish_ci' cannot be used in conjunction with 'binary' in call to regexp_like.
SELECT "ABC" REGEXP BINARY NULL;
ERROR HY000: Character set 'latin1_swedish_ci' cannot be used in conjunction with 'binary' in call to regexp_like.
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
#
# Bug#22836180: REGEXP, ASSERTION FAILED: !THD->IS_ERROR()
# IN SETUP_FIELDS()
#
DO 1 regexp (multilinestring(point(1,1)));
ERROR HY000: Incorrect arguments to multilinestring
#
# Bug#58026: massive recursion and crash in regular expression handling
#
SELECT '1' RLIKE RPAD('1', 10000, '(');
# Bug#33089668: Calling regexp_instr() with NULL causes all
#               further calls of it to return NULL
SELECT REGEXP_INSTR(e, 'pattern')
FROM (VALUES ROW('Find pattern'), ROW(NULL), ROW('Find pattern')) AS v(e);
REGEXP_INSTR(e, 'pattern')
6
NULL
6
SELECT REGEXP_LIKE(e, 'pattern')
FROM (VALUES ROW('Find pattern'), ROW(NULL), ROW('Find pattern')) AS v(e);
REGEXP_LIKE(e, 'pattern')
1
NULL
1
SELECT REGEXP_REPLACE(e, 'pattern', 'xyz')
FROM (VALUES ROW('Find pattern'), ROW(NULL), ROW('Find pattern')) AS v(e);
REGEXP_REPLACE(e, 'pattern', 'xyz')
Find xyz
NULL
Find xyz
SELECT REGEXP_SUBSTR(e, 'pattern')
FROM (VALUES ROW('Find pattern'), ROW(NULL), ROW('Find pattern')) AS v(e);
REGEXP_SUBSTR(e, 'pattern')
pattern
NULL
pattern
CREATE FUNCTION r_instr(input_text VARCHAR(255)) RETURNS INT DETERMINISTIC
RETURN REGEXP_INSTR(input_text, 'pattern');
SELECT r_instr('Find pattern');
r_instr('Find pattern')
6
SELECT r_instr(NULL);
r_instr(NULL)
NULL
SELECT r_instr('Find pattern');
r_instr('Find pattern')
6
DROP FUNCTION r_instr;
CREATE FUNCTION r_like(input_text VARCHAR(255)) RETURNS BOOLEAN DETERMINISTIC
RETURN REGEXP_LIKE(input_text, 'pattern');
SELECT r_like('Find pattern');
r_like('Find pattern')
1
SELECT r_like(NULL);
r_like(NULL)
NULL
SELECT r_like('Find pattern');
r_like('Find pattern')
1
DROP FUNCTION r_like;
CREATE FUNCTION r_replace(input_text VARCHAR(255))
RETURNS VARCHAR(255) DETERMINISTIC
RETURN REGEXP_REPLACE(input_text, 'pattern', 'xyz');
SELECT r_replace('Find pattern');
r_replace('Find pattern')
Find xyz
SELECT r_replace(NULL);
r_replace(NULL)
NULL
SELECT r_replace('Find pattern');
r_replace('Find pattern')
Find xyz
DROP FUNCTION r_replace;
CREATE FUNCTION r_substr(input_text VARCHAR(255))
RETURNS VARCHAR(255) DETERMINISTIC
RETURN REGEXP_SUBSTR(input_text, 'pattern');
SELECT r_substr('Find pattern');
r_substr('Find pattern')
pattern
SELECT r_substr(NULL);
r_substr(NULL)
NULL
SELECT r_substr('Find pattern');
r_substr('Find pattern')
pattern
DROP FUNCTION r_substr;