github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_regular_substr.test (about)

     1  SELECT REGEXP_SUBSTR('Thailand or Cambodia', 'l.nd') Result;
     2  
     3  SELECT REGEXP_SUBSTR('Lend for land', '^C') Result;
     4  
     5  SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 2) Result;
     6  
     7  SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 1, 1) 'Occurrence1';
     8  SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 1, 2) 'Occurrence2';
     9  SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 1, 3) 'Occurrence3';
    10  
    11  SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 2, 1) 'Occurrence1';
    12  SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 2, 2) 'Occurrence2';
    13  SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 2, 3) 'Occurrence3';
    14  
    15  SELECT REGEXP_SUBSTR(NULL, 'C.t', 2, 1);
    16  SELECT REGEXP_SUBSTR('Cat Cut Cot', NULL, 2, 1);
    17  SELECT REGEXP_SUBSTR(NULL, NULL, 2, 1);
    18  SELECT REGEXP_SUBSTR('Cat Cut Cot', "", 2, 1);
    19  
    20  create table t1(a int, b varchar(100));
    21  insert into t1 values(1 , "PowerSlave");
    22  insert into t1 values(2 , "Powerage");
    23  insert into t1 values( 3 , "Singing Down the Lane" );
    24  insert into t1 values(4 , "Ziltoid the Omniscient");
    25  insert into t1 values(5 , "Casualties of Cool");
    26  insert into t1 values( 6 , "Epicloud");
    27  insert into t1 values(7 , "Somewhere in Time");
    28  insert into t1 values(8 , "Piece of Mind");
    29  SELECT a, REGEXP_SUBSTR(b, '.i') from t1;
    30  drop table t1;
    31  
    32  create table t1(a int, b varchar(100));
    33  insert into t1 values(1 , "PowerSlave");
    34  insert into t1 values(2 , "Powerage");
    35  insert into t1 values( 3 , "Singing Down the Lane" );
    36  insert into t1 values(4 , "Ziltoid the Omniscient");
    37  insert into t1 values(5 , "Casualties of Cool");
    38  insert into t1 values( 6 , "Epicloud");
    39  insert into t1 values(7 , "Somewhere in Time");
    40  insert into t1 values(8 , "Piece of Mind");
    41  SELECT a, REGEXP_SUBSTR(b, '.i', 3) from t1;
    42  drop table t1;
    43  
    44  create table t1(a int, b varchar(100));
    45  insert into t1 values(1 , "PowerSlave");
    46  insert into t1 values(2 , "Powerage");
    47  insert into t1 values( 3 , "Singing Down the Lane" );
    48  insert into t1 values(4 , "Ziltoid the Omniscient");
    49  insert into t1 values(5 , "Casualties of Cool");
    50  insert into t1 values( 6 , "Epicloud");
    51  insert into t1 values(7 , "Somewhere in Time");
    52  insert into t1 values(8 , "Piece of Mind");
    53  SELECT a, REGEXP_SUBSTR(b, '.i', 3, 2) from t1;
    54  drop table t1;
    55  
    56  DROP TABLE IF EXISTS t;
    57  CREATE TABLE t(str1 VARCHAR(20), str2 CHAR(20));
    58  INSERT INTO t VALUES ('W * P', 'W + Z - O'), ('have has having', 'do does doing');
    59  INSERT INTO t VALUES ('XV*XZ', 'PP-ZZ-DXA'), ('aa bbb cc ddd', 'k ii lll oooo');
    60  SELECT REGEXP_SUBSTR(str1, '*'), REGEXP_SUBSTR(str2,'hav','hiv') FROM t;
    61  drop table t;