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