6
drop table if exists t1;
9
create table t1 (a varchar(10), key(a));
10
insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
11
explain extended select * from t1 where a like 'abc%';
12
explain extended select * from t1 where a like concat('abc','%');
13
select * from t1 where a like "abc%";
14
select * from t1 where a like concat("abc","%");
15
select * from t1 where a like "ABC%";
16
select * from t1 where a like "test%";
17
select * from t1 where a like "te_t";
20
# The following will test the Turbo Boyer-Moore code
22
select * from t1 where a like "%a%";
23
select * from t1 where a like "%abcd%";
24
select * from t1 where a like "%abc\d%";
28
create table t1 (a varchar(10), key(a));
33
insert into t1 values ('a'), ('a\\b');
34
select * from t1 where a like 'a\\%' escape '#';
35
select * from t1 where a like 'a\\%' escape '#' and a like 'a\\\\b';
38
# Bug #4200: Prepared statement parameter as argument to ESCAPE
40
prepare stmt1 from 'select * from t1 where a like \'a\\%\' escape ?';
42
execute stmt1 using @esc;
43
deallocate prepare stmt1;
48
# Bug #2885: like and datetime
51
create table t1 (a datetime);
52
insert into t1 values ('2004-03-11 12:00:21');
53
select * from t1 where a like '2004-03-11 12:00:21';
57
# Test like with non-default character set
62
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET koi8r);
64
INSERT INTO t1 VALUES ('����'),('����'),('����'),('����'),('����'),('����');
65
INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������');
66
INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������');
67
INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������');
69
SELECT * FROM t1 WHERE a LIKE '%����%';
70
SELECT * FROM t1 WHERE a LIKE '%���%';
71
SELECT * FROM t1 WHERE a LIKE '����%';
75
# Bug #2547 Strange "like" behaviour in tables with default charset=cp1250
76
# Test like with non-default character set using TurboBM
79
CREATE TABLE t1 (a varchar(250) NOT NULL) DEFAULT CHARACTER SET=cp1250;
81
('Techni Tapes Sp. z o.o.'),
82
('Pojazdy Szynowe PESA Bydgoszcz SA Holding'),
83
('AKAPESTER 1 P.P.H.U.'),
84
('Pojazdy Szynowe PESA Bydgoszcz S A Holding'),
85
('PPUH PESKA-I Maria Struniarska');
87
select * from t1 where a like '%PESA%';
88
select * from t1 where a like '%PESA %';
89
select * from t1 where a like '%PES%';
90
select * from t1 where a like '%PESKA%';
91
select * from t1 where a like '%ESKA%';
95
# LIKE crashed for binary collations in some cases
97
select _cp866'aaaaaaaaa' like _cp866'%aaaa%' collate cp866_bin;
100
# Check 8bit escape character
103
select 'andre%' like 'andre�%' escape '�';
105
# Check 8bit escape character with charset conversion:
106
# For "a LIKE b ESCAPE c" expressions,
107
# escape character is converted into the operation character set,
108
# which is result of aggregation of character sets of "a" and "b".
109
# "c" itself doesn't take part in aggregation, because its collation
110
# doesn't matter, escape character is always compared binary.
111
# In the example below, escape character is converted from koi8r into cp1251:
113
select _cp1251'andre%' like convert('andre�%' using cp1251) escape '�';