github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/function_split_part.result (about) 1 SELECT split_part('abc^123^ioo','^',1); 2 split_part(abc^123^ioo, ^, 1) 3 abc 4 SELECT split_part('efjq34','4r832r432094-3',2); 5 split_part(efjq34, 4r832r432094-3, 2) 6 null 7 SELECT split_part('床前明月光，疑是地上霜，举头望明月，低头思故乡','，',4); 8 split_part(床前明月光，疑是地上霜，举头望明月，低头思故乡, ，, 4) 9 低头思故乡 10 SELECT split_part('jewkrje的jdksvfe32893**(的——++））（）（ 的', '的', 3); 11 split_part(jewkrje的jdksvfe32893**(的——++））（）（ 的, 的, 3) 12 ——++））（）（ 13 SELECT split_part('fhew嗯嗯圣诞节chejwk嗯嗯__++__w嗯嗯','嗯嗯',2); 14 split_part(fhew嗯嗯圣诞节chejwk嗯嗯__++__w嗯嗯, 嗯嗯, 2) 15 圣诞节chejwk 16 SELECT split_part('v23dnnr###ewjrfkjewm#vrewnvrenjvnewmvrdjvrnjerewmvrjenjwvewmvrrnenjvrenjvrejnvewvrevrjewvrnew','ewmvr',8); 17 split_part(v23dnnr###ewjrfkjewm#vrewnvrenjvnewmvrdjvrnjerewmvrjenjwvewmvrrnenjvrenjvrejnvewvrevrjewvrnew, ewmvr, 8) 18 null 19 SELECT split_part('www.baidu.com','.',1); 20 split_part(www.baidu.com, ., 1) 21 www 22 SELECT split_part('43728943902493-24fjk43nmfjkwek432','3',3); 23 split_part(43728943902493-24fjk43nmfjkwek432, 3, 3) 24 90249 25 SELECT split_part('ABC*123*()(','*',2); 26 split_part(ABC*123*()(, *, 2) 27 123 28 SELECT split_part('12345*&+789*&dhejwfew2','*&',2); 29 split_part(12345*&+789*&dhejwfew2, *&, 2) 30 +789 31 SELECT split_part('.+0___=+. ','.',1); 32 split_part(.+0___=+. , ., 1) 33 34 SELECT split_part('..','.',1); 35 split_part(.., ., 1) 36 37 SELECT split_part(' ewfere.. ',' ',4); 38 split_part( ewfere.. , , 4) 39 40 SELECT split_part('','327832',1); 41 split_part(, 327832, 1) 42 43 SELECT split_part(NULL,'.',6); 44 split_part(null, ., 6) 45 null 46 SELECT split_part('-+0988 &^88?/7!@~~~~',NULL,3); 47 split_part(-+0988 &^88?/7!@~~~~, null, 3) 48 null 49 SELECT split_part('efwjkfe&*&**(*))))','*',NULL); 50 split_part(efwjkfe&*&**(*)))), *, null) 51 null 52 SELECT split_part('dfjwkfrewfr','r',0); 53 invalid input: split_part: field contains non-positive integer 54 SELECT split_part('ejwkvr&&(()))___hf真假ejw真假)','真假',-1); 55 Data truncation: data out of range: data type uint32, value '-1' 56 DROP TABLE IF EXISTS split_part_01; 57 CREATE TABLE split_part_01(id int, 58 s1 VARCHAR(100), 59 delim VARCHAR(20), 60 count1 smallint, 61 PRIMARY KEY(id)); 62 INSERT INTO split_part_01 VALUES(1, 'abc.com.cn','.',2); 63 INSERT INTO split_part_01 VALUES(2, '新年快乐，身体健康，万事如意', ',',3); 64 INSERT INTO split_part_01 VALUES(3, 'ehjwkvnrkew哈哈哈&9832哈哈哈,84321093,','哈哈',6); 65 INSERT INTO split_part_01 VALUES(4, '123abc&*.jjkmm&*.73290302','&*.',3); 66 INSERT INTO split_part_01 VALUES(5, ' 78829,.327hjfew.;,32oi cekw', ',',22); 67 INSERT INTO split_part_01 VALUES(6, 'efwu3nkjr3w3;; 9099032c45dc3s// * ',' ', 3242); 68 INSERT INTO split_part_01 VALUES(7, '83092i3f2o.dkwec<>dhwkjv<>789392-3<>', NULL, 3); 69 INSERT INTO split_part_01 VALUES(8, NULL, '.',11); 70 INSERT INTO split_part_01 VALUES(9, '442+562++++——----吃饭了',',',NULL); 71 INSERT INTO split_part_01 VALUES(1, 'ewjj32..3,l43/.43', 0); 72 Column count doesn't match value count at row 1 73 INSERT INTO split_part_01 VALUES(11, 'vhjdwewj3902i302o302($#$%^&*()_POJHFTY&(*UIOPL:<DQ87*q8JIFWJLWKMDXKLSMDXKSLMKCw54545484154444489897897o8u8&92)(','few',4); 74 internal error: Can't cast 'vhjdwewj3902i302o302($#$%!^(MISSING)&*()_POJHFTY&(*UIOPL:<DQ87*q8JIFWJLWKMDXKLSMDXKSLMKCw545454841544444898978...' to VARCHAR type. Src length 111 is larger than Dest length 100 75 INSERT INTO split_part_01 VALUES(12, '', 'vjdkelwvrew', 32769); 76 Data truncation: data out of range: data type int16, value '32769' 77 SELECT split_part(s1,NULL,count1) FROM split_part_01; 78 split_part(s1, null, count1) 79 null 80 null 81 null 82 null 83 null 84 null 85 null 86 null 87 null 88 SELECT split_part(s1,delim,NULL) FROM split_part_01; 89 split_part(s1, delim, null) 90 null 91 null 92 null 93 null 94 null 95 null 96 null 97 null 98 null 99 SELECT split_part(s1,delim,count1) FROM split_part_01; 100 split_part(s1, delim, count1) 101 com 102 null 103 null 104 73290302 105 null 106 null 107 null 108 null 109 null 110 SELECT split_part(s1,delim,count1) FROM split_part_01 WHERE count1 >= 2; 111 split_part(s1, delim, count1) 112 com 113 null 114 null 115 73290302 116 null 117 null 118 null 119 null 120 SELECT split_part(s1,delim,count1 + 3) FROM split_part_01 WHERE count1 < 0; 121 split_part(s1, delim, count1 + 3) 122 SELECT split_part(s1,delim,count1) FROM split_part_01 WHERE count1 = 3242; 123 split_part(s1, delim, count1) 124 null 125 SELECT * FROM split_part_01 WHERE split_part(s1,'.',2) = 'com'; 126 id s1 delim count1 127 1 abc.com.cn . 2 128 SELECT * FROM split_part_01 WHERE split_part(s1,'.',2) LIKE '%com%'; 129 id s1 delim count1 130 1 abc.com.cn . 2 131 SELECT * FROM split_part_01 WHERE split_part(s1,' ',3) = '78829,.327hjfew.;,32oi'; 132 id s1 delim count1 133 5 78829,.327hjfew.;,32oi cekw , 22 134 SELECT split_part(s1,' ',1) FROM split_part_01 WHERE id = 6; 135 split_part(s1, , 1) 136 efwu3nkjr3w3;; 137 SELECT split_part(s1,'*.',ABS(-2)) FROM split_part_01 WHERE id = 4; 138 split_part(s1, *., abs(-2)) 139 jjkmm& 140 SELECT * FROM split_part_01 WHERE split_part(s1, '.', 1 + 6) = 'com.cn'; 141 id s1 delim count1 142 SELECT split_part(split_part(s1,'.',22),'.',1) FROM split_part_01 WHERE id = 1; 143 split_part(split_part(s1, ., 22), ., 1) 144 null 145 SELECT * FROM split_part_01 WHERE LENGTH(split_part(s1,'*.',2)) = 6; 146 id s1 delim count1 147 4 123abc&*.jjkmm&*.73290302 &*. 3 148 SELECT * FROM split_part_01 WHERE split_part(LTRIM(s1),'.',2) = '.327hjfew.;'; 149 id s1 delim count1 150 SELECT delim,count1 FROM split_part_01 WHERE split_part(RTRIM(s1),'<>',1) = '83092i3f2o.dkwec'; 151 delim count1 152 null 3 153 SELECT * FROM split_part_01 WHERE LPAD(split_part(LTRIM(s1),'.',2),20,'*') = '************327hjfew'; 154 id s1 delim count1 155 5 78829,.327hjfew.;,32oi cekw , 22 156 SELECT * FROM split_part_01 WHERE RPAD(split_part(s1,'*.',3),20,'*') = '73290302************'; 157 id s1 delim count1 158 4 123abc&*.jjkmm&*.73290302 &*. 3 159 SELECT startswith(split_part(s1,'*.',3),'123') FROM split_part_01; 160 startswith(split_part(s1, *., 3), 123) 161 null 162 null 163 null 164 false 165 null 166 null 167 null 168 null 169 null 170 SELECT endswith(split_part(s1,'+',2),'62') FROM split_part_01; 171 endswith(split_part(s1, +, 2), 62) 172 null 173 null 174 null 175 null 176 null 177 null 178 null 179 null 180 true 181 SELECT * FROM split_part_01 WHERE find_in_set(split_part(s1,delim,count1),NULL) = NULL; 182 id s1 delim count1 183 SELECT CONCAT_WS(split_part(s1,delim,count1),'hehaha32789','ABCNSLK') FROM split_part_01 WHERE id = 2; 184 concat_ws(split_part(s1, delim, count1), hehaha32789, ABCNSLK) 185 null 186 SELECT empty(split_part(s1,delim,count1)) FROM split_part_01; 187 empty(split_part(s1, delim, count1)) 188 false 189 null 190 null 191 false 192 null 193 null 194 null 195 null 196 null 197 SELECT substring(split_part(s1,delim,count1),1,5) FROM split_part_01; 198 substring(split_part(s1, delim, count1), 1, 5) 199 com 200 null 201 null 202 73290 203 null 204 null 205 null 206 null 207 null 208 SELECT REVERSE(split_part(s1,delim,3)) FROM split_part_01; 209 reverse(split_part(s1, delim, 3)) 210 nc 211 null 212 ,39012348,哈 213 20309237 214 wkec io23 215 //s3cd54c2309909 216 null 217 null 218 null 219 SELECT * FROM split_part_01 WHERE s1 = (SELECT s1 FROM split_part_01 WHERE split_part(LTRIM(s1),'.',2) = '327hjfew'); 220 id s1 delim count1 221 5 78829,.327hjfew.;,32oi cekw , 22 222 SELECT(SELECT s1 FROM split_part_01 WHERE split_part(RTRIM(s1),'<>',1) = '83092i3f2o.dkwec'); 223 (select s1 from split_part_01 where split_part(rtrim(s1), <>, 1) = 83092i3f2o.dkwec) 224 83092i3f2o.dkwec<>dhwkjv<>789392-3<> 225 SELECT id ,s1, delim FROM split_part_01 WHERE s1 = (SELECT s1 FROM split_part_01 WHERE split_part(LTRIM(s1),'*.',1) = '123abc&'); 226 id s1 delim 227 4 123abc&*.jjkmm&*.73290302 &*. 228 DROP TABLE IF EXISTS split_part_02; 229 CREATE TABLE split_part_02(id int, 230 s1 longtext, 231 delim CHAR, 232 count1 int NOT NULL, 233 count2 bigint, 234 PRIMARY KEY(id)); 235 INSERT INTO split_part_02 VALUES(1, 'SUBSTRING函数的功能:用于从字符串的指定位置开始截取指定长度的字符串substring语法:SUBSTRING(string, start, length)','a',1231,548494515); 236 INSERT INTO split_part_02 VALUES(2, 'dvuewinviecfjds439432094ie3jiHHDIUWH*&*(UIJCSijfje3iu2j9032^&(*&()(*)I)A&^%^*&','j',3,123); 237 INSERT INTO split_part_02 VALUES(3, '', NULL, 1, 45); 238 INSERT INTO split_part_02 VALUES(4, NULL, '*', 5, NULL); 239 INSERT INTO split_part_02 VALUES(5, ' dhewjvrew er&&***&&n e89__+&&**+=--=*(&&***&(&^*)( ','*', 6, 83092302); 240 SELECT split_part(s1, NULL, count1) FROM split_part_02; 241 split_part(s1, null, count1) 242 null 243 null 244 null 245 null 246 null 247 SELECT split_part(s1, delim, NULL) FROM split_part_02; 248 split_part(s1, delim, null) 249 null 250 null 251 null 252 null 253 null 254 SELECT split_part(s1,delim,count1) FROM split_part_02; 255 split_part(s1, delim, count1) 256 null 257 iHHDIUWH*&*(UIJCSi 258 null 259 null 260 +=--= 261 SELECT split_part(s1,delim,3),split_part(s1,delim,4) FROM split_part_02; 262 split_part(s1, delim, 3) split_part(s1, delim, 4) 263 null null 264 iHHDIUWH*&*(UIJCSi f 265 null null 266 null null 267 &&n e89__+&& 268 SELECT split_part(s1,delim,count1) FROM split_part_02 WHERE count2 IS NOT NULL; 269 split_part(s1, delim, count1) 270 null 271 iHHDIUWH*&*(UIJCSi 272 null 273 +=--= 274 SELECT * FROM split_part_02 WHERE split_part(s1,'的',2) = '功能:用于从字符串'; 275 id s1 delim count1 count2 276 1 SUBSTRING函数的功能:用于从字符串的指定位置开始截取指定长度的字符串substring语法:SUBSTRING(string, start, length) a 1231 548494515 277 SELECT * FROM split_part_02 WHERE split_part(s1,'的',2)spilt( LIKE '功能%'; 278 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 61 near "spilt( LIKE '功能%!'(MISSING);"; 279 SELECT * FROM split_part_02 WHERE split_part(s1, 'iii', 3-1) = 'cn'; 280 id s1 delim count1 count2 281 SELECT * FROM split_part_02 WHERE LENGTH(split_part(s1,delim,2)) = 14; 282 id s1 delim count1 count2 283 2 dvuewinviecfjds439432094ie3jiHHDIUWH*&*(UIJCSijfje3iu2j9032^&(*&()(*)I)A&^%^*& j 3 123 284 SELECT split_part(LTRIM(s1),delim,6) FROM split_part_02 WHERE id = 5; 285 split_part(ltrim(s1), delim, 6) 286 +=--= 287 SELECT * FROM split_part_02 WHERE split_part(LTRIM(s1),delim,6) = '+=--='; 288 id s1 delim count1 count2 289 5 dhewjvrew er&&***&&n e89__+&&**+=--=*(&&***&(&^*)( * 6 83092302 290 SELECT delim,count1 FROM split_part_02 WHERE split_part(RTRIM(s1),delim,3) = NULL; 291 delim count1 292 SELECT * FROM split_part_02 WHERE LPAD(split_part(LTRIM(s1),'ew',2),20,'*') = '*****************jvr'; 293 id s1 delim count1 count2 294 5 dhewjvrew er&&***&&n e89__+&&**+=--=*(&&***&(&^*)( * 6 83092302 295 SELECT startswith(split_part(s1,delim,3),'SUB') FROM split_part_02; 296 startswith(split_part(s1, delim, 3), SUB) 297 null 298 false 299 null 300 null 301 false 302 SELECT endswith(split_part(s1,delim,6),'h)') FROM split_part_02; 303 endswith(split_part(s1, delim, 6), h)) 304 null 305 false 306 null 307 null 308 false 309 SELECT find_in_set(split_part(s1,delim,count1),'SUBSTRING函数的功能:用于从字符串的指定位置开始截取指定长度的字符串substring语法:SUBSTRING(string, start, length),dvuewinviecfjds439432094ie3jiHHDIUWH*&*(UIJCSijfje3iu2j9032^&(*&()(*)I)A&^%^*&') FROM split_part_02; 310 find_in_set(split_part(s1, delim, count1), SUBSTRING函数的功能:用于从字符串的指定位置开始截取指定长度的字符串substring语法:SUBSTRING(string, start, length),dvuewinviecfjds439432094ie3jiHHDIUWH*&*(UIJCSijfje3iu2j9032^&(*&()(*)I)A&^%^*&) 311 null 312 0 313 null 314 null 315 0 316 SELECT CONCAT_WS(split_part(s1,delim,count1),'hehaha32789','ABCNSLK') FROM split_part_02 WHERE id = 2; 317 concat_ws(split_part(s1, delim, count1), hehaha32789, ABCNSLK) 318 hehaha32789iHHDIUWH*&*(UIJCSiABCNSLK 319 SELECT REVERSE(split_part(s1,delim,3)) FROM split_part_02; 320 reverse(split_part(s1, delim, 3)) 321 null 322 iSCJIU(*&*HWUIDHHi 323 null 324 null 325 326 DROP TABLE IF EXISTS split_part_03; 327 DROP TABLE IF EXISTS split_part_04; 328 CREATE TABLE split_part_03( 329 id int, 330 d1 CHAR, 331 str1 VARCHAR(50), 332 primary key (id)); 333 CREATE TABLE split_part_04( 334 id int, 335 d2 smallint, 336 str1 mediumtext NOT NULL, 337 primary key (id)); 338 INSERT INTO split_part_03 VALUES(1, ')', '78213)jji)JIJSC_)dhej'); 339 INSERT INTO split_part_03 VALUES(2, '', '***((((()))'); 340 INSERT INTO split_part_03 VALUES(3, ' ', NULL); 341 INSERT INTO split_part_03 VALUES(4, NULL, ' hciuwejw^&*((*&*^GGHJjqm'); 342 INSERT INTO split_part_03 VALUES(5, '*',' fjewlk*(&^de jw*(&^wuio*(&^,,,, '); 343 INSERT INTO split_part_04 VALUES(1, 1, '78213)jji)JIJSC_ )dhej '); 344 INSERT INTO split_part_04 VALUES(2, 90, 'jewjeioqjeio3j4729u3ewqiu(U)(JOIWJ***((((()))'); 345 INSERT INTO split_part_04 VALUES(3, NULL,' hciuwejw^&*((*&'); 346 INSERT INTO split_part_04 VALUES(4, 6, ' hciuwejw^&*(*^GGHJjqmmqjJHGG'); 347 SELECT split_part_03.id AS id_3,split_part_04.str1 AS str1_4 FROM split_part_03,split_part_04 WHERE split_part(split_part_03.str1, ')', 2) = split_part(split_part_04.str1, ')', 2); 348 id_3 str1_4 349 1 78213)jji)JIJSC_ )dhej 350 SELECT split_part_03.str1 AS tmp,split_part_04.str1 AS temp FROM split_part_03 left join split_part_04 ON split_part(split_part_03.str1, '2', 1) = split_part(split_part_04.str1, '2', 1); 351 tmp temp 352 78213)jji)JIJSC_)dhej 78213)jji)JIJSC_ )dhej 353 ***((((())) null 354 null null 355 hciuwejw^&*((*&*^GGHJjqm null 356 fjewlk*(&^de jw*(&^wuio*(&^,,,, null 357 SELECT split_part_03.d1 AS d1_3,split_part_04.d2 AS d2_4 FROM split_part_03 right join split_part_04 ON split_part(split_part_03.str1, '2', 1) = split_part(split_part_04.str1, '2', 1); 358 d1_3 d2_4 359 ) 1 360 null 90 361 null null 362 null 6