github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/function_split_part.sql (about)

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