github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/function_substring_index.sql (about) 1 -- @suit 2 -- @case 3 -- @test function SUBSTRING_INDEX(str,delim,count) 4 -- @label:bvt 5 6 SELECT substring_index('abc^123^ioo','^',1); 7 SELECT substring_index('efjq34','4r832r432094-3',2); 8 SELECT substring_index('床前明月光，疑是地上霜，举头望明月，低头思故乡','，',4); 9 SELECT substring_index('jewkrje的jdksvfe32893**(的——++））（）（ 的', '的', -3); 10 SELECT substring_index('fhew嗯嗯圣诞节chejwk嗯嗯__++__w嗯嗯','嗯嗯',2); 11 SELECT substring_index('v23dnnr###ewjrfkjewm#vrewnvrenjvnewmvrdjvrnjerewmvrjenjwvewmvrrnenjvrenjvrejnvewvrevrjewvrnew','ewmvr',8); 12 SELECT substring_index('www.baidu.com','.',-1); 13 SELECT substring_index('hdjwkrfew*(dehw382*(vnejw4','*(',-5); 14 SELECT substring_index('43728943902493-24fjk43nmfjkwek432','3',3); 15 SELECT substring_index('dfjwkfrewfr','r',0); 16 SELECT substring_index('ABC*123*()(','*',2); 17 SELECT substring_index('12345*&+789*&dhejwfew2','*&',2); 18 SELECT substring_index('.+0___=+. ','.',1); 19 SELECT substring_index('..','.',1); 20 SELECT substring_index('','327832',1); 21 SELECT substring_index(NULL,'.',6); 22 SELECT substring_index('-+0988 &^88?/7!@~~~~',NULL,3); 23 SELECT substring_index('efwjkfe&*&**(*))))','*',NULL); 24 25 26 -- @suite 27 -- @setup 28 DROP TABLE IF EXISTS substring_index_01; 29 CREATE TABLE substring_index_01(id int, 30 s1 VARCHAR(100), 31 delim VARCHAR(20), 32 count1 smallint, 33 PRIMARY KEY(id)); 34 35 36 INSERT INTO substring_index_01 VALUES(1, 'abc.com.cn','.',2); 37 INSERT INTO substring_index_01 VALUES(2, '新年快乐，身体健康，万事如意', ',',3); 38 INSERT INTO substring_index_01 VALUES(3, 'ehjwkvnrkew哈哈哈&9832哈哈哈,84321093,','哈哈',-6); 39 INSERT INTO substring_index_01 VALUES(4, '123abc&*.jjkmm&*.73290302','&*.',3); 40 INSERT INTO substring_index_01 VALUES(5, ' 78829,.327hjfew.;,32oi cekw', ',',-2); 41 INSERT INTO substring_index_01 VALUES(6, 'efwu3nkjr3w3;; 9099032c45dc3s// * ',' ', -4); 42 INSERT INTO substring_index_01 VALUES(7, '','',0); 43 INSERT INTO substring_index_01 VALUES(8, '83092i3f2o.dkwec<>dhwkjv<>789392-3<>', NULL, 3); 44 INSERT INTO substring_index_01 VALUES(9, NULL, '.',11); 45 INSERT INTO substring_index_01 VALUES(10, '442+562++++——----吃饭了',',',NULL); 46 47 48 -- Abnormal insert 49 INSERT INTO substring_index_01 VALUES(1, 'ewjj32..3,l43/.43', 0); 50 INSERT INTO substring_index_01 VALUES(11, 'vhjdwewj3902i302o302($#$%^&*()_POJHFTY&(*UIOPL:<DQ87*q8JIFWJLWKMDXKLSMDXKSLMKCw54545484154444489897897o8u8&92)(','few',4); 51 INSERT INTO substring_index_01 VALUES(12, '', 'vjdkelwvrew', 32769); 52 53 54 SELECT substring_index(s1,delim,count1) FROM substring_index_01; 55 SELECT substring_index(s1,delim,count1) FROM substring_index_01 WHERE count1 >= 2; 56 SELECT substring_index(s1,delim,count1 + 3) FROM substring_index_01 WHERE count1 < 0; 57 SELECT substring_index(s1,delim,count1 % 2) FROM substring_index_01 WHERE count1 % 2 = 0; 58 SELECT * FROM substring_index_01 WHERE substring_index(s1,'.',2) = 'abc.com'; 59 SELECT * FROM substring_index_01 WHERE substring_index(s1,' ',-3) = '* '; 60 SELECT substring_index(s1,' ',-10) FROM substring_index_01 WHERE id = 6; 61 SELECT substring_index(s1,'*.',ABS(-2)) FROM substring_index_01 WHERE id = 4; 62 SELECT * FROM substring_index_01 WHERE substring_index(s1, '.', 1 - 3) = 'com.cn'; 63 SELECT substring_index(substring_index(s1,'.',-2),'.',1) FROM substring_index_01 WHERE id = 1; 64 65 66 -- -- Nested with string functions 67 SELECT * FROM substring_index_01 WHERE LENGTH(substring_index(s1,'*.',2)) = 15; 68 SELECT * FROM substring_index_01 WHERE substring_index(LTRIM(s1),'.',2) = '78829,.327hjfew'; 69 SELECT delim,count1 FROM substring_index_01 WHERE substring_index(RTRIM(s1),'<>',1) = '83092i3f2o.dkwec'; 70 SELECT * FROM substring_index_01 WHERE LPAD(substring_index(LTRIM(s1),'.',2),20,'*') = '83092i3f2o.dkwec****'; 71 SELECT RPAD(substring_index(s1,'*.',3),20,'*') FROM substring_index_01 WHERE id = 4; 72 SELECT startswith(substring_index(s1,'*.',3),'123') FROM substring_index_01; 73 SELECT endswith(substring_index(s1,'+',2),'62') FROM substring_index_01; 74 SELECT * FROM substring_index_01 WHERE find_in_set(substring_index(s1,delim,count1),NULL) = NULL; 75 SELECT CONCAT_WS(substring_index(s1,delim,count1),'hehaha32789','ABCNSLK') FROM substring_index_01 WHERE id = 2; 76 SELECT empty(substring_index(s1,delim,count1)) FROM substring_index_01; 77 SELECT substring(substring_index(s1,delim,count1),1,5) FROM substring_index_01; 78 SELECT REVERSE(substring_index(s1,delim,3)) FROM substring_index_01; 79 80 81 -- subquries 82 SELECT * FROM substring_index_01 WHERE s1 = (SELECT s1 FROM substring_index_01 WHERE substring_index(LTRIM(s1),'.',2) = '78829,.327hjfew'); 83 SELECT(SELECT s1 FROM substring_index_01 WHERE substring_index(RTRIM(s1),'<>',1) = '83092i3f2o.dkwec'); 84 SELECT id ,s1, delim FROM substring_index_01 WHERE s1 = (SELECT s1 FROM substring_index_01 WHERE substring_index(LTRIM(s1),'.',0) = NULL); 85 86 87 -- @suite 88 -- @setup 89 DROP TABLE IF EXISTS substring_index_02; 90 CREATE TABLE substring_index_02(id int, 91 s1 longtext, 92 delim CHAR, 93 count1 int NOT NULL, 94 count2 bigint unsigned, 95 PRIMARY KEY(id)); 96 97 98 INSERT INTO substring_index_02 VALUES(1, 'SUBSTRING函数的功能:用于从字符串的指定位置开始截取指定长度的字符串substring语法:SUBSTRING(string, start, length)','a',-1231,548494515); 99 INSERT INTO substring_index_02 VALUES(2, 'dvuewinviecfjds439432094ie3jiHHDIUWH*&*(UIJCSijfje3iu2j9032^&(*&()(*)I)A&^%^*&','j',0,123); 100 INSERT INTO substring_index_02 VALUES(3, '', NULL, -3, 45); 101 INSERT INTO substring_index_02 VALUES(4, NULL, '*', 5, NULL); 102 INSERT INTO substring_index_02 VALUES(5, ' dhewjvrew er&&***&&n e89__+&&**+=--=*(&&***&(&^*)( ','*', 6, 83092302); 103 104 105 SELECT substring_index(s1,delim,count1) FROM substring_index_02; 106 SELECT substring_index(s1,delim,count1 + 3),substring_index(s1,delim,count2) FROM substring_index_02 WHERE count1 < 0; 107 SELECT substring_index(s1,delim,count2 % 2) FROM substring_index_02 WHERE count2 IS NOT NULL; 108 SELECT * FROM substring_index_02 WHERE substring_index(s1,'的',2) = 'SUBSTRING函数的功能:用于从字符串'; 109 SELECT * FROM substring_index_02 WHERE substring_index(s1, 'iii', 1 - 3) = 'com.cn'; 110 111 112 -- -- Nested with string functions 113 SELECT * FROM substring_index_02 WHERE LENGTH(substring_index(s1,delim,2)) = 27; 114 SELECT * FROM substring_index_02 WHERE substring_index(LTRIM(s1),delim,count1) = 'dhewjvrew er&&***&&n e89__+&&**+=--='; 115 SELECT delim,count1 FROM substring_index_02 WHERE substring_index(RTRIM(s1),delim,3) = '&&***&(&^*)( '; 116 SELECT * FROM substring_index_02 WHERE LPAD(substring_index(LTRIM(s1),'e',3),20,'*') = 'dhewjvrew *********'; 117 SELECT startswith(substring_index(s1,delim,3),'SUB') FROM substring_index_02; 118 SELECT endswith(substring_index(s1,delim,-2),'h)') FROM substring_index_02; 119 SELECT find_in_set(substring_index(s1,delim,count1),'SUBSTRING函数的功能:用于从字符串的指定位置开始截取指定长度的字符串substring语法:SUBSTRING(string, start, length),dvuewinviecfjds439432094ie3jiHHDIUWH*&*(UIJCSijfje3iu2j9032^&(*&()(*)I)A&^%^*&') FROM substring_index_02; 120 SELECT CONCAT_WS(substring_index(s1,delim,count1),'hehaha32789','ABCNSLK') FROM substring_index_02 WHERE id = 2; 121 SELECT substring(substring_index(s1,delim,count2),0,10) FROM substring_index_02; 122 SELECT REVERSE(substring_index(s1,delim,3)) FROM substring_index_02; 123 124 125 -- @suite 126 -- @setup 127 DROP TABLE IF EXISTS substring_index_03; 128 DROP TABLE IF EXISTS substring_index_04; 129 CREATE TABLE substring_index_03( 130 id int, 131 d1 CHAR, 132 str1 VARCHAR(50), 133 primary key (id)); 134 135 CREATE TABLE substring_index_04( 136 id int, 137 d2 smallint, 138 str1 mediumtext NOT NULL, 139 primary key (id)); 140 141 142 INSERT INTO substring_index_03 VALUES(1, ')', '78213)jji)JIJSC_)dhej'); 143 INSERT INTO substring_index_03 VALUES(2, '', '***((((()))'); 144 INSERT INTO substring_index_03 VALUES(3, ' ', NULL); 145 INSERT INTO substring_index_03 VALUES(4, NULL, ' hciuwejw^&*((*&*^GGHJjqm'); 146 INSERT INTO substring_index_03 VALUES(5, '*',' fjewlk*(&^de jw*(&^wuio*(&^,,,, '); 147 148 INSERT INTO substring_index_04 VALUES(1, 0, '78213)jji)JIJSC_ )dhej '); 149 INSERT INTO substring_index_04 VALUES(2, 90, 'jewjeioqjeio3j4729u3ewqiu(U)(JOIWJ***((((()))'); 150 INSERT INTO substring_index_04 VALUES(3, NULL,' hciuwejw^&*((*&'); 151 INSERT INTO substring_index_04 VALUES(4, -6, ' hciuwejw^&*(*^GGHJjqmmqjJHGG'); 152 153 154 -- join 155 SELECT * FROM substring_index_03 WHERE str1 = (SELECT str1 FROM substring_index_04 WHERE substring_index(substring_index_04.str1, '(', 4) = '***((('); 156 SELECT * FROM substring_index_03,substring_index_04 WHERE substring_index(substring_index_03.str1, ')', 2) = substring_index(substring_index_04.str1, ')', 2); 157 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); 158 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); 159 160