github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/operator/like_operator.result (about) 1 SELECT 'a' LIKE 'ae'; 2 a like ae 3 false 4 SELECT 'ae' LIKE 'a'; 5 ae like a 6 false 7 SELECT 'MYSQL' LIKE 'mysql'; 8 MYSQL like mysql 9 false 10 SELECT 'David!' LIKE 'David_'; 11 David! like David_ 12 true 13 SELECT 'David!' LIKE '%D%v%'; 14 David! like %D%v% 15 true 16 SELECT 'David!' LIKE 'David\_'; 17 David! like David\_ 18 false 19 SELECT 'David_' LIKE 'David\_'; 20 David_ like David\_ 21 false 22 DROP TABLE IF EXISTS like_test; 23 CREATE TABLE like_test( 24 str1 VARCHAR(50) 25 ); 26 INSERT INTO like_test VALUES('D:'); 27 INSERT INTO like_test VALUES('D:\\'); 28 INSERT INTO like_test VALUES('D:\\System_files'); 29 INSERT INTO like_test VALUES('D:\\System_files\\'); 30 INSERT INTO like_test VALUES(NULL); 31 INSERT INTO like_test VALUES('NULL'); 32 INSERT INTO like_test VALUES(' A'); 33 INSERT INTO like_test VALUES('s _'); 34 SELECT * FROM like_test WHERE str1 LIKE '%:'; 35 str1 36 D: 37 SELECT * FROM like_test WHERE str1 LIKE '%\_%'; 38 str1 39 D:\System_files 40 D:\System_files\ 41 s _ 42 SELECT * FROM like_test WHERE str1 LIKE '___'; 43 str1 44 D:\ 45 A 46 s _ 47 SELECT * FROM like_test WHERE str1 LIKE ' %'; 48 str1 49 A 50 SELECT * FROM like_test WHERE str1 LIKE NULL; 51 str1 52 SELECT * FROM like_test WHERE str1 LIKE '_:%'; 53 str1 54 D: 55 D:\ 56 D:\System_files 57 D:\System_files\ 58 SELECT * FROM like_test WHERE str1 LIKE 's__'; 59 str1 60 s _ 61 SELECT * FROM like_test WHERE str1 LIKE '%s'; 62 str1 63 D:\System_files 64 SELECT * FROM like_test WHERE str1 LIKE 'd%'; 65 str1 66 SELECT * FROM like_test WHERE str1 LIKE '%%'; 67 str1 68 D: 69 D:\ 70 D:\System_files 71 D:\System_files\ 72 NULL 73 A 74 s _ 75 SELECT * FROM like_test WHERE str1 IS NULL; 76 str1 77 null 78 SELECT * FROM like_test WHERE str1 LIKE '%U%'; 79 str1 80 NULL 81 SELECT * FROM like_test WHERE str1 LIKE '%:%' AND str1 LIKE '%s%'; 82 str1 83 D:\System_files 84 D:\System_files\ 85 SELECT * FROM like_test WHERE str1 LIKE '' AND str1 LIKE 's%'; 86 str1 87 SELECT * FROM like_test WHERE str1 LIKE '% %' AND str1 LIKE '%N%'; 88 str1 89 SELECT * FROM like_test WHERE str1 LIKE '%L' OR str1 LIKE '%s%'; 90 str1 91 D:\System_files 92 D:\System_files\ 93 NULL 94 s _ 95 SELECT * FROM like_test WHERE str1 LIKE '% %' OR str1 LIKE '%N%'; 96 str1 97 NULL 98 A 99 s _ 100 SELECT * FROM (SELECT * FROM like_test WHERE str1 LIKE 'D%') AS a WHERE LENGTH(str1) > 4; 101 str1 102 D:\System_files 103 D:\System_files\ 104 SELECT str1 FROM (SELECT * FROM like_test WHERE str1 LIKE '_:%') AS T WHERE str1 LIKE '%S%'; 105 str1 106 D:\System_files 107 D:\System_files\ 108 SELECT * FROM like_test WHERE str1 NOT LIKE '___'; 109 str1 110 D: 111 D:\System_files 112 D:\System_files\ 113 NULL 114 SELECT * FROM like_test WHERE str1 NOT LIKE 'D%'; 115 str1 116 NULL 117 A 118 s _ 119 SELECT * FROM like_test WHERE str1 NOT LIKE '%\_%'; 120 str1 121 D: 122 D:\ 123 NULL 124 A 125 SELECT * FROM like_test WHERE str1 NOT LIKE 'NULL'; 126 str1 127 D: 128 D:\ 129 D:\System_files 130 D:\System_files\ 131 A 132 s _ 133 SELECT * FROM like_test WHERE str1 NOT LIKE NULL; 134 str1 135 SELECT * FROM like_test WHERE str1 NOT LIKE ''; 136 str1 137 D: 138 D:\ 139 D:\System_files 140 D:\System_files\ 141 NULL 142 A 143 s _ 144 SELECT COUNT(*) FROM like_test WHERE str1 LIKE '%baz%'; 145 count(*) 146 0 147 SELECT COUNT(*) FROM like_test WHERE str1 NOT LIKE '%baz%'; 148 count(*) 149 7 150 SELECT COUNT(*) FROM like_test WHERE str1 NOT LIKE '%baz%' OR str1 IS NULL; 151 count(*) 152 8 153 SELECT str1, str1 LIKE '%\\' FROM like_test; 154 str1 str1 like %\\ 155 D: false 156 D:\ true 157 D:\System_files false 158 D:\System_files\ true 159 null null 160 NULL false 161 A false 162 s _ false 163 SELECT str1, str1 LIKE '%\\\\' FROM like_test; 164 [unknown result because it is related to issue#5078] 165 DELETE FROM like_test; 166 INSERT INTO like_test VALUES('99.9'); 167 INSERT INTO like_test VALUES('88'); 168 INSERT INTO like_test VALUES('89.88'); 169 INSERT INTO like_test VALUES(19.88); 170 INSERT INTO like_test VALUES(2887.08); 171 SELECT * FROM like_test WHERE str1 LIKE '9%'; 172 str1 173 99.9 174 SELECT * FROM like_test WHERE str1 LIKE '%9'; 175 str1 176 99.9 177 SELECT * FROM like_test WHERE str1 LIKE '%88'; 178 str1 179 88 180 89.88 181 19.88 182 SELECT * FROM like_test WHERE str1 NOT LIKE '%88'; 183 str1 184 99.9 185 2887.08 186 DELETE FROM like_test; 187 INSERT INTO like_test VALUES('ABC1_23D'); 188 INSERT INTO like_test VALUES('123ABCD\\'); 189 INSERT INTO like_test VALUES('ABCD\\123'); 190 INSERT INTO like_test VALUES(' '); 191 SELECT * FROM like_test WHERE str1 LIKE '%D\\%'; 192 str1 193 SELECT * FROM like_test WHERE str1 LIKE '% '; 194 str1 195 196 SELECT * FROM like_test WHERE str1 LIKE '%3_'; 197 str1 198 ABC1_23D 199 DROP TABLE IF EXISTS like_test; 200 CREATE TABLE like_test( 201 str1 VARCHAR(50), 202 str2 CHAR(50) 203 ); 204 INSERT INTO like_test(str1, str2) VALUES('CHINA IS OUR HOMELAND', 'BEIJING IS THE CAPITAL'); 205 INSERT INTO like_test(str1, str2) VALUES('DHINA IS OUR HOMELAN ', 'THIS MAN PUSH HIM'); 206 INSERT INTO like_test(str1, str2) VALUES(' HINA IS OUR HOMELAND%', 'HE LIKE WHORE'); 207 INSERT INTO like_test(str1, str2) VALUES('THE UNITED OF AMERICAN_', 'NORTH AMERICA'); 208 INSERT INTO like_test(str1, str2) VALUES('AB%CDE', 'PDFXLXDOC'); 209 INSERT INTO like_test(str1, str2) VALUES('THE UNITED OF ENGLAND\\', "KINGDOM'"); 210 INSERT INTO like_test(str1, str2) VALUES(' ', ''); 211 INSERT INTO like_test(str1, str2) VALUES('', NULL); 212 INSERT INTO like_test(str1, str2) VALUES('法的萨菲厄张三三三三三', '考虑理论可看见年年年年年年'); 213 SELECT NULL LIKE 'ABC', 'ABC' LIKE NULL; 214 null like ABC ABC like null 215 null null 216 SELECT * FROM like_test WHERE str1 LIKE 'C%'; 217 str1 str2 218 CHINA IS OUR HOMELAND BEIJING IS THE CAPITAL 219 SELECT * FROM like_test WHERE str1 LIKE '% '; 220 str1 str2 221 DHINA IS OUR HOMELAN THIS MAN PUSH HIM 222 223 SELECT * FROM like_test WHERE str1 LIKE '%s%'; 224 str1 str2 225 SELECT * FROM like_test WHERE str1 LIKE '%C%' AND str1 LIKE '%D%'; 226 str1 str2 227 CHINA IS OUR HOMELAND BEIJING IS THE CAPITAL 228 THE UNITED OF AMERICAN_ NORTH AMERICA 229 AB%CDE PDFXLXDOC 230 SELECT * FROM like_test WHERE str2 LIKE '__I%'; 231 str1 str2 232 CHINA IS OUR HOMELAND BEIJING IS THE CAPITAL 233 DHINA IS OUR HOMELAN THIS MAN PUSH HIM 234 SELECT * FROM like_test WHERE str2 LIKE '__ %'; 235 str1 str2 236 HINA IS OUR HOMELAND% HE LIKE WHORE 237 SELECT * FROM like_test WHERE str2 LIKE ''; 238 str1 str2 239 240 SELECT * FROM like_test WHERE str2 LIKE NULL; 241 str1 str2 242 SELECT * FROM like_test WHERE str1 LIKE '%\\'; 243 str1 str2 244 THE UNITED OF ENGLAND\ KINGDOM' 245 SELECT * FROM like_test WHERE str2 LIKE '%\''; 246 str1 str2 247 THE UNITED OF ENGLAND\ KINGDOM' 248 SELECT * FROM like_test WHERE str1 LIKE '%三__'; 249 str1 str2 250 法的萨菲厄张三三三三三 考虑理论可看见年年年年年年 251 SELECT * FROM like_test WHERE str1 LIKE '%\%'; 252 [unknown result because it is related to issue#5056] 253 SELECT * FROM like_test WHERE str1 NOT LIKE 'D%'; 254 str1 str2 255 CHINA IS OUR HOMELAND BEIJING IS THE CAPITAL 256 HINA IS OUR HOMELAND% HE LIKE WHORE 257 THE UNITED OF AMERICAN_ NORTH AMERICA 258 AB%CDE PDFXLXDOC 259 THE UNITED OF ENGLAND\ KINGDOM' 260 261 null 262 法的萨菲厄张三三三三三 考虑理论可看见年年年年年年 263 SELECT * FROM like_test WHERE str1 NOT LIKE '%E'; 264 str1 str2 265 CHINA IS OUR HOMELAND BEIJING IS THE CAPITAL 266 DHINA IS OUR HOMELAN THIS MAN PUSH HIM 267 HINA IS OUR HOMELAND% HE LIKE WHORE 268 THE UNITED OF AMERICAN_ NORTH AMERICA 269 THE UNITED OF ENGLAND\ KINGDOM' 270 271 null 272 法的萨菲厄张三三三三三 考虑理论可看见年年年年年年 273 DELETE FROM like_test; 274 DROP TABLE like_test; 275 DROP TABLE IF EXISTS chinese_test; 276 CREATE TABLE chinese_test( 277 name VARCHAR(50), 278 home VARCHAR(100), 279 job VARCHAR(20) 280 ); 281 INSERT INTO chinese_test VALUES('张三', '河南省信阳市桥东区广平小区', '软件工程师'); 282 INSERT INTO chinese_test VALUES('张飞', '北京市朝阳区天宫苑桥东街道', '教师'); 283 INSERT INTO chinese_test VALUES('里斯', '广东省广州村', '学生教师'); 284 INSERT INTO chinese_test VALUES('刘五', '%%%江苏省南京市金区陵新村', '%%工人'); 285 SELECT name FROM chinese_test WHERE home LIKE '%桥东%'; 286 name 287 张三 288 张飞 289 SELECT * FROM chinese_test WHERE name LIKE '张%'; 290 name home job 291 张三 河南省信阳市桥东区广平小区 软件工程师 292 张飞 北京市朝阳区天宫苑桥东街道 教师 293 SELECT * FROM (SELECT * FROM chinese_test WHERE name LIKE '张%') AS a WHERE home LIKE '%广%'; 294 name home job 295 张三 河南省信阳市桥东区广平小区 软件工程师 296 SELECT * FROm (SELECT * FROM chinese_test WHERE job LIKE '%师') AS a WHERE home LIKE '%市%'; 297 name home job 298 张三 河南省信阳市桥东区广平小区 软件工程师 299 张飞 北京市朝阳区天宫苑桥东街道 教师 300 SELECT name FROM chinese_test WHERE home LIKE '\%%' AND job LIKE '\%%'; 301 name 302 刘五 303 SELECT * FROM chinese_test WHERE home LIKE '%省%区'; 304 name home job 305 张三 河南省信阳市桥东区广平小区 软件工程师 306 DROP TABLE chinese_test; 307 CREATE TABLE stu( 308 id INT PRIMARY KEY, 309 name VARCHAR(20), 310 class INT, 311 sex VARCHAR(2), 312 address VARCHAR(50) 313 ); 314 CREATE TABLE score( 315 id INT PRIMARY KEY, 316 name VARCHAR(20), 317 chinese FLOAT, 318 math FLOAT, 319 english FLOAT 320 ); 321 INSERT INTO stu VALUES('1001', 'JACK', 7, 'M', 'US'); 322 INSERT INTO stu VALUES('1002', 'TOM', 7, '', 'UK'); 323 INSERT INTO stu VALUES('1005', 'ROMMY', 8, 'F', 'CHINA'); 324 INSERT INTO stu VALUES('1006', 'RACK', 8, 'M', 'ITALY'); 325 INSERT INTO score VALUES('1001', 'JACK', 89.5, 45.2, 67.5); 326 INSERT INTO score VALUES('1002', 'TOM', 58.5, 76, 78); 327 INSERT INTO score VALUES('1005', 'ROMMY', 68, 55, 69); 328 SELECT * FROM stu INNER JOIN score ON stu.id = score.id WHERE stu.name LIKE '_O%'; 329 id name class sex address id name chinese math english 330 1002 TOM 7 UK 1002 TOM 58.5 76.0 78.0 331 1005 ROMMY 8 F CHINA 1005 ROMMY 68.0 55.0 69.0 332 SELECT * FROM stu INNER JOIN score ON stu.id = score.id WHERE score.math > 60 AND stu.address LIKE 'U%_'; 333 id name class sex address id name chinese math english 334 1002 TOM 7 UK 1002 TOM 58.5 76.0 78.0 335 SELECT * FROM stu RIGHT JOIN score ON stu.id = score.id WHERE score.english > 65 AND stu.sex LIKE '%'; 336 id name class sex address id name chinese math english 337 1001 JACK 7 M US 1001 JACK 89.5 45.2 67.5 338 1002 TOM 7 UK 1002 TOM 58.5 76.0 78.0 339 1005 ROMMY 8 F CHINA 1005 ROMMY 68.0 55.0 69.0 340 SELECT * FROM stu WHERE address LIKE '%A%' AND name LIKE '%R%'; 341 id name class sex address 342 1005 ROMMY 8 F CHINA 343 1006 RACK 8 M ITALY 344 SELECT * FROM stu INNER JOIN score ON stu.id = score.id WHERE stu.address NOT LIKE 'CH%'; 345 id name class sex address id name chinese math english 346 1001 JACK 7 M US 1001 JACK 89.5 45.2 67.5 347 1002 TOM 7 UK 1002 TOM 58.5 76.0 78.0 348 SELECT * FROM stu WHERE name NOT LIKE '%A%'; 349 id name class sex address 350 1002 TOM 7 UK 351 1005 ROMMY 8 F CHINA 352 SELECT 353 stu.id, stu.name, stu.address, a.chinese, a.english 354 FROM 355 (SELECT * FROM score WHERE math < 60 AND name LIKE '%M__') AS a,stu 356 WHERE 357 a.id = stu.id AND stu.address LIKE '%H%'; 358 id name address chinese english 359 1005 ROMMY CHINA 68.0 69.0 360 SELECT 361 stu.name, stu.address 362 FROM 363 (SELECT * FROm score WHERE chinese BETWEEN 60 AND 70) AS a, stu 364 WHERE 365 a.id = stu.id AND address LIKE SUBSTRING('THE CHINA',5); 366 name address 367 ROMMY CHINA 368 SELECT 369 stu.id, stu.name, stu.address, score.english 370 FROM 371 stu 372 INNER JOIN 373 score 374 ON 375 stu.id = score.id 376 WHERE stu.sex NOT LIKE ''; 377 id name address english 378 1001 JACK US 67.5 379 1005 ROMMY CHINA 69.0 380 DROP TABLE IF EXISTS stu; 381 DROP TABLE IF EXISTS score; 382 383 create table t1(a tinyint, b smallint, c int, d bigint); 384 insert into t1 values(121, 121, 121, 121); 385 select * from t1 where (a like '%2%' and b like '%2%' and c like '%2%' and d like '%2%'); 386 a b c d 387 121 121 121 121 388 drop table t1; 389 390 create table t1(a tinyint unsigned, b smallint unsigned, c int unsigned, d bigint unsigned); 391 insert into t1 values(121, 121, 121, 121); 392 select * from t1 where (a like '%2%' and b like '%2%' and c like '%2%' and d like '%2%'); 393 a b c d 394 121 121 121 121 395 drop table t1; 396 397 create table t1(a text); 398 insert into t1 values(rpad('1',50000,'1') + rpad('1',50000,'1')); 399 select * from t1 where a like "."; 400 a 401 drop table t1; 402 create table t1(a varchar(20)); 403 insert into t1 values ('abc'), ('ABC'), ('abC'); 404 select * from t1 where a ilike '%abC%'; 405 a 406 abc 407 ABC 408 abC 409 show variables where value ilike "%system%" and variable_name = "time_zone"; 410 Variable_name Value 411 time_zone SYSTEM