func_like.result 3.15 KB
Newer Older
unknown's avatar
unknown committed
1 2 3
drop table if exists t1;
create table t1 (a varchar(10), key(a));
insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
4
explain select * from t1 where a like 'abc%';
unknown's avatar
unknown committed
5
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
6
1	SIMPLE	t1	index	a	a	11	NULL	5	Using where; Using index
7
explain select * from t1 where a like concat('abc','%');
unknown's avatar
unknown committed
8
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
9
1	SIMPLE	t1	index	a	a	11	NULL	5	Using where; Using index
unknown's avatar
unknown committed
10
select * from t1 where a like "abc%";
11 12 13
a
abc
abcd
14 15 16 17
select * from t1 where a like concat("abc","%");
a
abc
abcd
unknown's avatar
unknown committed
18
select * from t1 where a like "ABC%";
19
a
unknown's avatar
unknown committed
20 21
abc
abcd
unknown's avatar
unknown committed
22
select * from t1 where a like "test%";
unknown's avatar
unknown committed
23
a
24
test
unknown's avatar
unknown committed
25
select * from t1 where a like "te_t";
26 27
a
test
28 29 30 31 32 33 34 35 36 37 38
select * from t1 where a like "%a%";
a
a
abc
abcd
select * from t1 where a like "%abcd%";
a
abcd
select * from t1 where a like "%abc\d%";
a
abcd
unknown's avatar
unknown committed
39
drop table t1;
40 41 42 43 44
create table t1 (a varchar(10), key(a));
insert into t1 values ('a'), ('a\\b');
select * from t1 where a like 'a\\%' escape '#';
a
a\b
45 46 47
select * from t1 where a like 'a\\%' escape '#' and a like 'a\\\\b';
a
a\b
48
drop table t1;
unknown's avatar
unknown committed
49 50 51 52 53 54
create table t1 (a datetime);
insert into t1 values ('2004-03-11 12:00:21');
select * from t1 where a like '2004-03-11 12:00:21';
a
2004-03-11 12:00:21
drop table t1;
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
SET NAMES koi8r;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET koi8r);
INSERT INTO t1 VALUES (''),(''),(''),(''),(''),('');
INSERT INTO t1 VALUES (''),(''),(''),('');
INSERT INTO t1 VALUES (''),(''),(''),('');
INSERT INTO t1 VALUES (''),(''),(''),('');
SELECT * FROM t1 WHERE a LIKE '%%';
a


















SELECT * FROM t1 WHERE a LIKE '%%';
a


















SELECT * FROM t1 WHERE a LIKE '%';
a


















DROP TABLE t1;
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
SET NAMES cp1250;
CREATE TABLE t1 (a varchar(250) NOT NULL) DEFAULT CHARACTER SET=cp1250;
INSERT INTO t1 VALUES
('Techni Tapes Sp. z o.o.'),
('Pojazdy Szynowe PESA Bydgoszcz SA Holding'),
('AKAPESTER 1 P.P.H.U.'),
('Pojazdy Szynowe PESA Bydgoszcz S A Holding'),
('PPUH PESKA-I Maria Struniarska');
select * from t1 where a like '%PESA%';
a
Pojazdy Szynowe PESA Bydgoszcz SA Holding
Pojazdy Szynowe PESA Bydgoszcz S A Holding
select * from t1 where a like '%PESA %';
a
Pojazdy Szynowe PESA Bydgoszcz SA Holding
Pojazdy Szynowe PESA Bydgoszcz S A Holding
select * from t1 where a like '%PES%';
a
Techni Tapes Sp. z o.o.
Pojazdy Szynowe PESA Bydgoszcz SA Holding
AKAPESTER 1 P.P.H.U.
Pojazdy Szynowe PESA Bydgoszcz S A Holding
PPUH PESKA-I Maria Struniarska
select * from t1 where a like '%PESKA%';
a
PPUH PESKA-I Maria Struniarska
select * from t1 where a like '%ESKA%';
a
PPUH PESKA-I Maria Struniarska
DROP TABLE t1;