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

     1  SELECT REGEXP_SUBSTR('Thailand or Cambodia', 'l.nd') Result;
     2  result
     3  land
     4  SELECT REGEXP_SUBSTR('Lend for land', '^C') Result;
     5  result
     6  NULL
     7  SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 2) Result;
     8  result
     9  Cut
    10  SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 1, 1) 'Occurrence1';
    11  Occurrence1
    12  Cat
    13  SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 1, 2) 'Occurrence2';
    14  Occurrence2
    15  Cut
    16  SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 1, 3) 'Occurrence3';
    17  Occurrence3
    18  Cot
    19  SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 2, 1) 'Occurrence1';
    20  Occurrence1
    21  Cut
    22  SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 2, 2) 'Occurrence2';
    23  Occurrence2
    24  Cot
    25  SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 2, 3) 'Occurrence3';
    26  Occurrence3
    27  NULL
    28  SELECT REGEXP_SUBSTR(NULL, 'C.t', 2, 1);
    29  regexp_substr(null, C.t, 2, 1)
    30  NULL
    31  SELECT REGEXP_SUBSTR('Cat Cut Cot', NULL, 2, 1);
    32  regexp_substr(Cat Cut Cot, null, 2, 1)
    33  NULL
    34  SELECT REGEXP_SUBSTR(NULL, NULL, 2, 1);
    35  regexp_substr(null, null, 2, 1)
    36  NULL
    37  SELECT REGEXP_SUBSTR('Cat Cut Cot', "", 2, 1);
    38  regexp_substr(Cat Cut Cot, , 2, 1)
    39  NULL
    40  create table t1(a int, b varchar(100));
    41  insert into t1 values(1 , "PowerSlave");
    42  insert into t1 values(2 , "Powerage");
    43  insert into t1 values( 3 , "Singing Down the Lane" );
    44  insert into t1 values(4 , "Ziltoid the Omniscient");
    45  insert into t1 values(5 , "Casualties of Cool");
    46  insert into t1 values( 6 , "Epicloud");
    47  insert into t1 values(7 , "Somewhere in Time");
    48  insert into t1 values(8 , "Piece of Mind");
    49  SELECT a, REGEXP_SUBSTR(b, '.i') from t1;
    50  a    regexp_substr(b, .i)
    51  1    null
    52  2    null
    53  3    Si
    54  4    Zi
    55  5    ti
    56  6    pi
    57  7     i
    58  8    Pi
    59  drop table t1;
    60  create table t1(a int, b varchar(100));
    61  insert into t1 values(1 , "PowerSlave");
    62  insert into t1 values(2 , "Powerage");
    63  insert into t1 values( 3 , "Singing Down the Lane" );
    64  insert into t1 values(4 , "Ziltoid the Omniscient");
    65  insert into t1 values(5 , "Casualties of Cool");
    66  insert into t1 values( 6 , "Epicloud");
    67  insert into t1 values(7 , "Somewhere in Time");
    68  insert into t1 values(8 , "Piece of Mind");
    69  SELECT a, REGEXP_SUBSTR(b, '.i', 3) from t1;
    70  a    regexp_substr(b, .i, 3)
    71  1    null
    72  2    null
    73  3    gi
    74  4    oi
    75  5    ti
    76  6    null
    77  7     i
    78  8    Mi
    79  drop table t1;
    80  create table t1(a int, b varchar(100));
    81  insert into t1 values(1 , "PowerSlave");
    82  insert into t1 values(2 , "Powerage");
    83  insert into t1 values( 3 , "Singing Down the Lane" );
    84  insert into t1 values(4 , "Ziltoid the Omniscient");
    85  insert into t1 values(5 , "Casualties of Cool");
    86  insert into t1 values( 6 , "Epicloud");
    87  insert into t1 values(7 , "Somewhere in Time");
    88  insert into t1 values(8 , "Piece of Mind");
    89  SELECT a, REGEXP_SUBSTR(b, '.i', 3, 2) from t1;
    90  a    regexp_substr(b, .i, 3, 2)
    91  1    null
    92  2    null
    93  3    null
    94  4    ni
    95  5    null
    96  6    null
    97  7    Ti
    98  8    null
    99  drop table t1;
   100  DROP TABLE IF EXISTS t;
   101  CREATE TABLE t(str1 VARCHAR(20), str2 CHAR(20));
   102  INSERT INTO t VALUES ('W * P', 'W + Z - O'), ('have has having', 'do does doing');
   103  INSERT INTO t VALUES ('XV*XZ', 'PP-ZZ-DXA'), ('aa bbb cc ddd', 'k ii lll oooo');
   104  SELECT REGEXP_SUBSTR(str1, '*'), REGEXP_SUBSTR(str2,'hav','hiv') FROM t;
   105  invalid argument cast to int, bad value hiv
   106  drop table t;