github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/function_instr.result (about) 1 SELECT INSTR('ejwnqke','wn'); 2 instr(ejwnqke, wn) 3 3 4 SELECT INSTR('wn','ejwnqke'); 5 instr(wn, ejwnqke) 6 0 7 SELECT INSTR('hvjdke3qj','a'); 8 instr(hvjdke3qj, a) 9 0 10 SELECT INSTR('今天是晴天ok.are yioeore;wmv','晴天'); 11 instr(今天是晴天ok.are yioeore;wmv, 晴天) 12 4 13 SELECT INSTR('ewhihjreiwhvrejw8344332￥#……@#@￥#@￥DSCSVRERGEWvefw',''); 14 instr(ewhihjreiwhvrejw8344332￥#……@#@￥#@￥DSCSVRERGEWvefw, ) 15 1 16 SELECT INSTR('edhjw 38902&A**',' '); 17 instr(edhjw 38902&A**, ) 18 6 19 SELECT INSTR('reuwYHWJMQ781///-+++','fe3232'); 20 instr(reuwYHWJMQ781///-+++, fe3232) 21 0 22 SELECT INSTR('',''); 23 instr(, ) 24 1 25 SELECT INSTR('','ehwj32'); 26 instr(, ehwj32) 27 0 28 SELECT INSTR('251625%$#@*(ejf2f32f',''); 29 instr(251625%$#@*(ejf2f32f, ) 30 1 31 SELECT INSTR(NULL,'ewqe'); 32 instr(null, ewqe) 33 null 34 SELECT INSTR('24e8w7/*37289',NULL); 35 instr(24e8w7/*37289, null) 36 null 37 SELECT INSTR(NULL,NULL); 38 instr(null, null) 39 null 40 DROP TABLE IF EXISTS instr_01; 41 CREATE TABLE instr_01(id int, 42 str1 CHAR, 43 str2 VARCHAR(30), 44 PRIMARY KEY(id)); 45 INSERT INTO instr_01 VALUES(1, 'a', 'dhehw'); 46 INSERT INTO instr_01 VALUES(2, '2', '372890932'); 47 INSERT INTO instr_01 VALUES(3, '*', 'fejkj4kl332342'); 48 INSERT INTO instr_01 VALUES(4, '-', ' 4ej348324*&&^*&*--++'); 49 INSERT INTO instr_01 VALUES(5, '.', '3h2h3kj2*()____ ))() '); 50 INSERT INTO instr_01 VALUES(6, '', NULL); 51 INSERT INTO instr_01 VALUES(7, NULL, ''); 52 INSERT INTO instr_01 VALUES(8, NULL, NULL); 53 SELECT INSTR(str2,str1) FROM instr_01; 54 instr(str2, str1) 55 0 56 3 57 0 58 18 59 0 60 null 61 null 62 null 63 SELECT INSTR(str2,'') FROM instr_01 WHERE str2 IS NOT NULL; 64 instr(str2, ) 65 1 66 1 67 1 68 1 69 1 70 1 71 SELECT INSTR('',str1) FROM instr_01; 72 instr(, str1) 73 0 74 0 75 0 76 0 77 0 78 1 79 null 80 null 81 SELECT INSTR(NULL, str2) FROM instr_01; 82 instr(null, str2) 83 null 84 null 85 null 86 null 87 null 88 null 89 null 90 null 91 SELECT INSTR(str1, NULL) FROM instr_01; 92 instr(str1, null) 93 null 94 null 95 null 96 null 97 null 98 null 99 null 100 null 101 SELECT * FROM instr_01 WHERE instr(str2,'qke') = 5; 102 id str1 str2 103 SELECT id,str1,str2 FROM instr_01 WHERE instr(str2, '32') = NULL; 104 id str1 str2 105 SELECT * FROM instr_01 WHERE id = (SELECT id FROM instr_01 WHERE instr(str2,'qke') = 5); 106 id str1 str2 107 SELECT(SELECT str2 FROM instr_01 WHERE instr(str1,'a') = 1),id FROM instr_01; 108 (select str2 from instr_01 where instr(str1, a) = 1) id 109 dhehw 1 110 dhehw 2 111 dhehw 3 112 dhehw 4 113 dhehw 5 114 dhehw 6 115 dhehw 7 116 dhehw 8 117 SELECT id ,str1, str2 FROM instr_01 WHERE id = (SELECT id FROM instr_01 WHERE instr(str2,'aaa') = NULL); 118 id str1 str2 119 SELECT concat_ws('-',INSTR(str2,str1),INSTR('abc','a')) FROM instr_01; 120 concat_ws(-, instr(str2, str1), instr(abc, a)) 121 0-1 122 3-1 123 0-1 124 18-1 125 0-1 126 1 127 1 128 1 129 SELECT find_in_set(INSTR(str2,str1),'hdkewqjfew-') FROM instr_01 WHERE id = 4; 130 find_in_set(instr(str2, str1), hdkewqjfew-) 131 0 132 SELECT oct(INSTR(str2,str1)) FROM instr_01 WHERE id = 4; 133 oct(instr(str2, str1)) 134 22 135 SELECT empty(instr(str2,str1)) FROM instr_01; 136 empty(instr(str2, str1)) 137 false 138 false 139 false 140 false 141 false 142 null 143 null 144 null 145 SELECT LENGTH(INSTR(str2,str1)) FROM instr_01; 146 length(instr(str2, str1)) 147 1 148 1 149 1 150 2 151 1 152 null 153 null 154 null 155 SELECT INSTR(LTRIM(str2),'*&&^*&') FROM instr_01 WHERE id = 4; 156 instr(ltrim(str2), *&&^*&) 157 10 158 SELECT INSTR(RTRIM(str2),'()____ )') FROM instr_01 WHERE str1 = '.'; 159 instr(rtrim(str2), ()____ )) 160 10 161 SELECT LPAD(INSTR(str2, str1), 10, '-') FROM instr_01; 162 lpad(instr(str2, str1), 10, -) 163 ---------0 164 ---------3 165 ---------0 166 --------18 167 ---------0 168 null 169 null 170 null 171 SELECT RPAD(INSTR(str2, 3), 5, '***') FROM instr_01; 172 rpad(instr(str2, 3), 5, ***) 173 0**** 174 1**** 175 9**** 176 5**** 177 1**** 178 null 179 0**** 180 null 181 SELECT substring(instr(str1,'a'),'321421') FROM instr_01; 182 substring(instr(str1, a), 321421) 183 184 185 186 187 188 189 null 190 null 191 SELECT INSTR(REVERSE(str2), ' ') FROM instr_01; 192 instr(reverse(str2), ) 193 0 194 0 195 0 196 21 197 1 198 null 199 0 200 null 201 SELECT bin(INSTR(str1, 'a')) FROM instr_01 WHERE id = 1; 202 bin(instr(str1, a)) 203 1 204 SELECT hex(INSTR(str2, 'w')) FROM instr_01 WHERE id = 1; 205 hex(instr(str2, w)) 206 5 207 DROP TABLE IF EXISTS instr_02; 208 CREATE TABLE instr_02(id int, 209 str1 mediumtext, 210 str2 VARCHAR(30) NOT NULL); 211 INSERT INTO instr_02 VALUES(1, '今天是很美好的一天 Today is a wonderful day!!!', '美好'); 212 INSERT INTO instr_02 VALUES(2, '4**-1+83982j4mfkerwvuh43oij3f42j4iuu32oi4ejf32j432YUDINWKJ<DJ>>A>欢迎使用mo',''); 213 INSERT INTO instr_02 VALUES(3, '', 'gchjewqhedjw'); 214 INSERT INTO instr_02 VALUES(4, '', ''); 215 INSERT INTO instr_02 VALUES(5, NULL,'abcd'); 216 INSERT INTO instr_02 VALUES(6, ' ewfew3324 ed_+_+ ', 'ew'); 217 SELECT * FROM instr_02 WHERE INSTR(str1,str2) = 5; 218 id str1 str2 219 1 今天是很美好的一天 Today is a wonderful day!!! 美好 220 SELECT INSTR(str1, str2) FROM instr_02; 221 instr(str1, str2) 222 5 223 1 224 0 225 1 226 null 227 4 228 SELECT INSTR(str2, str1) FROM instr_02; 229 instr(str2, str1) 230 0 231 0 232 1 233 1 234 null 235 0 236 SELECT * FROM instr_02 WHERE id = (SELECT id FROM instr_02 WHERE INSTR(str1,'+8') = 6); 237 id str1 str2 238 2 4**-1+83982j4mfkerwvuh43oij3f42j4iuu32oi4ejf32j432YUDINWKJ<DJ>>A>欢迎使用mo 239 SELECT(SELECT str2 FROM instr_02 WHERE instr(str1,'a') = 1),id FROM instr_02; 240 (select str2 from instr_02 where instr(str1, a) = 1) id 241 null 1 242 null 2 243 null 3 244 null 4 245 null 5 246 null 6 247 SELECT concat_ws('-',INSTR(str2,str1),INSTR('abc','a')) FROM instr_02; 248 concat_ws(-, instr(str2, str1), instr(abc, a)) 249 0-1 250 0-1 251 1-1 252 1-1 253 1 254 0-1 255 SELECT find_in_set(INSTR(str2,str1),'hdkewqjfew-') FROM instr_02 WHERE id = 4; 256 find_in_set(instr(str2, str1), hdkewqjfew-) 257 0 258 SELECT oct(INSTR(str2,str1)) FROM instr_02 WHERE id = 4; 259 oct(instr(str2, str1)) 260 1 261 SELECT empty(instr(str2,str1)) FROM instr_02; 262 empty(instr(str2, str1)) 263 false 264 false 265 false 266 false 267 null 268 false 269 SELECT LENGTH(INSTR(str2,str1)) FROM instr_02; 270 length(instr(str2, str1)) 271 1 272 1 273 1 274 1 275 null 276 1 277 SELECT INSTR(LTRIM(str1),'ed_+_+') FROM instr_02 WHERE id = 6; 278 instr(ltrim(str1), ed_+_+) 279 13 280 SELECT INSTR(RTRIM(str1),'3324') FROM instr_02 WHERE id = 6; 281 instr(rtrim(str1), 3324) 282 9 283 SELECT LPAD(INSTR(str2, str1), 6, 'abc') FROM instr_02; 284 lpad(instr(str2, str1), 6, abc) 285 abcab0 286 abcab0 287 abcab1 288 abcab1 289 null 290 abcab0 291 SELECT RPAD(INSTR(str2, 3), 5, '') FROM instr_02; 292 rpad(instr(str2, 3), 5, ) 293 294 295 296 297 298 299 SELECT INSTR(substring(str2, 1, 6), 'cd') FROM instr_02 WHERE id = 5; 300 instr(substring(str2, 1, 6), cd) 301 3 302 SELECT INSTR(REVERSE(str1), '用使') FROM instr_02 WHERE id = 2; 303 instr(reverse(str1), 用使) 304 3 305 SELECT bin(INSTR(str2, 'd')) FROM instr_02 WHERE id = 3; 306 bin(instr(str2, d)) 307 1010 308 SELECT hex(INSTR(str1, 'ed')) FROM instr_02 WHERE id = 6; 309 hex(instr(str1, ed)) 310 10 311 DROP TABLE IF EXISTS instr_03; 312 DROP TABLE IF EXISTS instr_04; 313 CREATE TABLE instr_03( 314 id int, 315 d1 CHAR, 316 str1 VARCHAR(50), 317 primary key (id)); 318 CREATE TABLE instr_04( 319 id int, 320 str1 mediumtext NOT NULL, 321 primary key (id)); 322 INSERT INTO instr_03 VALUES(1, 'a', 'zheshimeihaodeyitian,这是美好的一天'); 323 INSERT INTO instr_03 VALUES(2, '*', '明天更美好ehwqknjcw*^*qk67329&&*'); 324 INSERT INTO instr_03 VALUES(3, NULL, 'ewgu278wd-+ABNJDSK'); 325 INSERT INTO instr_03 VALUES(4, '', NULL); 326 INSERT INTO instr_04 VALUES(1, '盼望着,盼望着,东风来了,春天的脚步近了。 一切都像刚睡醒的样子,欣欣然张开了眼。山朗润起来了,水涨 起来了,太阳的脸红起来了。 小草偷偷地从土里钻出来，Choose to Be Alone on Purpose Here we are, all by ourselves, all 22 million of us by recent count, alone in our rooms'); 327 INSERT INTO instr_04 VALUES(2, 'zheshimeihaodeyitian,这是美好的一天'); 328 INSERT INTO instr_04 VALUES(3, 'ewgu278wd-+ABNJDSK'); 329 INSERT INTO instr_04 VALUES(4, 'hey32983..........,,'); 330 SELECT * FROM instr_03 WHERE id = (SELECT id FROM instr_04 WHERE INSTR(str1,'u27')); 331 id d1 str1 332 3 null ewgu278wd-+ABNJDSK 333 SELECT instr_03.id AS id_3,instr_03.id AS id_4 FROM instr_03,instr_04 WHERE INSTR(instr_03.str1,'shi') = INSTR(instr_04.str1,'美好'); 334 id_3 id_4 335 2 2 336 2 2 337 2 2 338 3 3 339 3 3 340 3 3 341 SELECT instr_03.str1 AS str1_3,instr_03.str1 FROM instr_03,instr_04 WHERE INSTR(instr_03.str1,'meihao') = INSTR(instr_04.str1,'meihao'); 342 str1_3 str1 343 zheshimeihaodeyitian,这是美好的一天 zheshimeihaodeyitian,这是美好的一天 344 明天更美好ehwqknjcw*^*qk67329&&* 明天更美好ehwqknjcw*^*qk67329&&* 345 明天更美好ehwqknjcw*^*qk67329&&* 明天更美好ehwqknjcw*^*qk67329&&* 346 明天更美好ehwqknjcw*^*qk67329&&* 明天更美好ehwqknjcw*^*qk67329&&* 347 ewgu278wd-+ABNJDSK ewgu278wd-+ABNJDSK 348 ewgu278wd-+ABNJDSK ewgu278wd-+ABNJDSK 349 ewgu278wd-+ABNJDSK ewgu278wd-+ABNJDSK 350 SELECT INSTR(instr_03.str1, 'ABNJDSK') FROM instr_03,instr_04 WHERE instr_03.str1 = instr_04.str1; 351 instr(instr_03.str1, ABNJDSK) 352 0 353 12 354 SELECT * FROM instr_03 WHERE str1 = (SELECT str1 FROM instr_04 WHERE INSTR(str1,'ABNJDSK') = 12); 355 id d1 str1 356 3 null ewgu278wd-+ABNJDSK 357 SELECT INSTR(instr_03.str1, 'zheshi')AS tmp, instr_04.str1 AS temp FROM instr_03 join instr_04 ON instr_03.str1 = instr_04.str1; 358 tmp temp 359 1 zheshimeihaodeyitian,这是美好的一天 360 0 ewgu278wd-+ABNJDSK 361 SELECT instr_03.id AS id_3,instr_04.id AS id_4 FROM instr_03 left join instr_04 ON instr_03; 362 invalid input: column instr_03 does not exist