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

     1  SELECT SUBSTRING_INDEX('www.mysql.com', '.', 0);
     2  SELECT SUBSTRING_INDEX('www.mysql.com', '.', 1);
     3  SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
     4  SELECT SUBSTRING_INDEX('www.mysql.com', '.', 3);
     5  SELECT SUBSTRING_INDEX('www.mysql.com', '.', -3);
     6  SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
     7  SELECT SUBSTRING_INDEX('www.mysql.com', '.', -1);
     8  SELECT SUBSTRING_INDEX('www.mysql.com', '', 0);
     9  
    10  SELECT SUBSTRING_INDEX('www.mysql.com', '.', '0');
    11  SELECT SUBSTRING_INDEX('www.mysql.com', '.', '1');
    12  SELECT SUBSTRING_INDEX('www.mysql.com', '.', '2');
    13  SELECT SUBSTRING_INDEX('www.mysql.com', '.', '3');
    14  SELECT SUBSTRING_INDEX('www.mysql.com', '.', '4');
    15  SELECT SUBSTRING_INDEX('www.mysql.com', '.', '-3');
    16  SELECT SUBSTRING_INDEX('www.mysql.com', '.', '-2');
    17  SELECT SUBSTRING_INDEX('www.mysql.com', '.', '-1');
    18  SELECT SUBSTRING_INDEX('www.mysql.com', '', '0');
    19  
    20  
    21  select substring_index('xyz', 'abc', 9223372036854775808);
    22  select substring_index("aaa.bbb.ccc.ddd.eee",'.',18446744073709551613);
    23  select substring_index("aaa.bbb.ccc.ddd.eee",'.',-18446744073709551613);
    24      
    25  
    26  select substring_index(null, 'abc', 9223372036854775808);
    27  select substring_index('xyz', null, 9223372036854775808);
    28  select substring_index('xyz', 'abc', null);
    29  
    30  SELECT SUBSTRING_INDEX('192,168,8,203', ',', 1);
    31  SELECT SUBSTRING_INDEX('192,168,8,203', ',', -1);
    32  SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('192,168,8,203', ',', 2), ',',-1);
    33  SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('192,168,8,203', ',', -2), ',',1);
    34  
    35  
    36  create table test(a varchar(100), b varchar(20), c int);
    37  insert into test values('www.mysql.com', '.', 0);
    38  insert into test values('www.mysql.com', '.', 1);
    39  insert into test values('www.mysql.com', '.', 2);
    40  insert into test values('www.mysql.com', '.', 3);
    41  insert into test values('www.mysql.com', '.', 9223372036854775808);
    42  insert into test values('www.mysql.com', '.', -1);
    43  insert into test values('www.mysql.com', '.', -2);
    44  insert into test values('www.mysql.com', '.', -3);
    45  select SUBSTRING_INDEX(a, b, c) from test;
    46  drop table test;
    47  
    48  
    49  create table test(a varchar(100), b varchar(20), c float);
    50  insert into test values('www.mysql.com', '.', 0);
    51  insert into test values('www.mysql.com', '.', 1.0);
    52  insert into test values('www.mysql.com', '.', 2.0);
    53  insert into test values('www.mysql.com', '.', 3.0);
    54  insert into test values('www.mysql.com', '.', 92233720368547758.0);
    55  insert into test values('www.mysql.com', '.', -1.0);
    56  insert into test values('www.mysql.com', '.', -2.0);
    57  insert into test values('www.mysql.com', '.', -3.0);
    58  select SUBSTRING_INDEX(a, b, c) from test;
    59  drop table test;
    60  
    61  
    62  create table test(a varchar(100), b varchar(20), c varchar(100));
    63  insert into test values('www.mysql.com', '.', '0');
    64  insert into test values('www.mysql.com', '.', '1');
    65  insert into test values('www.mysql.com', '.', '2');
    66  insert into test values('www.mysql.com', '.', '3');
    67  insert into test values('www.mysql.com', '.', '-1');
    68  insert into test values('www.mysql.com', '.', '-2');
    69  insert into test values('www.mysql.com', '.', '-3');
    70  select SUBSTRING_INDEX(a, b, c) from test;
    71  drop table test;
    72  
    73  create table test(a text, b varchar(20), c int);
    74  insert into test values('www.mysql.com', '.', 0);
    75  insert into test values('www.mysql.com', '.', 1);
    76  insert into test values('www.mysql.com', '.', 2);
    77  insert into test values('www.mysql.com', '.', 3);
    78  insert into test values('www.mysql.com', '.', 9223372036854775808);
    79  insert into test values('www.mysql.com', '.', -1);
    80  insert into test values('www.mysql.com', '.', -2);
    81  insert into test values('www.mysql.com', '.', -3);
    82  select SUBSTRING_INDEX(a, b, c) from test;
    83  drop table test;
    84  
    85  create table test(a blob, b varchar(20), c int);
    86  insert into test values('www.mysql.com', '.', 0);
    87  insert into test values('www.mysql.com', '.', 1);
    88  insert into test values('www.mysql.com', '.', 2);
    89  insert into test values('www.mysql.com', '.', 3);
    90  insert into test values('www.mysql.com', '.', 9223372036854775808);
    91  insert into test values('www.mysql.com', '.', -1);
    92  insert into test values('www.mysql.com', '.', -2);
    93  insert into test values('www.mysql.com', '.', -3);
    94  select SUBSTRING_INDEX(a, b, c) from test;
    95  drop table test;