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

     1  SELECT REGEXP_INSTR('Cat', 'at') Result;
     2  SELECT REGEXP_INSTR('Cat', '^at') Result;
     3  SELECT REGEXP_INSTR('at', '^at') Result;
     4  SELECT REGEXP_INSTR('Cat Cat', 'Cat', 2) Result;
     5  
     6  SELECT REGEXP_INSTR('Cat Cat', 'Cat', 2) AS 'Pos2';
     7  SELECT REGEXP_INSTR('Cat Cat', 'Cat', 3) AS 'Pos3'; 
     8  SELECT REGEXP_INSTR('Cat Cat', 'Cat', 5) AS 'Pos5';
     9  
    10  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1) 'Pos1';
    11  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2) 'Pos2';
    12  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 6) 'Pos6';
    13  
    14  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1) 'Occurrence1';
    15  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 2) 'Occurrence2';
    16  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 3) 'Occurrence3';
    17  
    18  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2, 1) 'Occurrence1';
    19  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2, 2) 'Occurrence2';
    20  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2, 3) 'Occurrence3';
    21  
    22  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 0) 'Option0';
    23  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 1) 'Option1';
    24  
    25  SELECT REGEXP_INSTR('Cat', null) Result;
    26  SELECT REGEXP_INSTR(null, 'C.t') Result;
    27  SELECT REGEXP_INSTR(null,  null) Result;
    28  
    29  create table t1(a int, b varchar(100));
    30  insert into t1 values(1 , "PowerSlave");
    31  insert into t1 values(2 , "Powerage");
    32  insert into t1 values( 3 , "Singing Down the Lane" );
    33  insert into t1 values(4 , "Ziltoid the Omniscient");
    34  insert into t1 values(5 , "Casualties of Cool");
    35  insert into t1 values( 6 , "Epicloud");
    36  insert into t1 values(7 , "Somewhere in Time");
    37  insert into t1 values(8 , "Piece of Mind");
    38  SELECT a, REGEXP_INSTR(b, '^P') from t1;
    39  drop table t1;
    40  
    41  create table t1(a int, b varchar(100));
    42  insert into t1 values(1 , "PowerSlave");
    43  insert into t1 values(2 , "Powerage");
    44  insert into t1 values( 3 , "Singing Down the Lane" );
    45  insert into t1 values(4 , "Ziltoid the Omniscient");
    46  insert into t1 values(5 , "Casualties of Cool");
    47  insert into t1 values( 6 , "Epicloud");
    48  insert into t1 values(7 , "Somewhere in Time");
    49  insert into t1 values(8 , "Piece of Mind");
    50  SELECT a, b from t1 WHERE REGEXP_INSTR(b, '^P') = 1;
    51  drop table t1;
    52  create table t1(a int, b varchar(100));
    53  insert into t1 values(1 , "PowerSlave");
    54  insert into t1 values(2 , "Powerage");
    55  insert into t1 values( 3 , "Singing Down the Lane" );
    56  insert into t1 values(4 , "Ziltoid the Omniscient");
    57  insert into t1 values(5 , "Casualties of Cool");
    58  insert into t1 values( 6 , "Epicloud");
    59  insert into t1 values(7 , "Somewhere in Time");
    60  insert into t1 values(8 , "Piece of Mind");
    61  SELECT a, REGEXP_INSTR(b, 'i', 3) from t1;
    62  drop table t1;
    63  
    64  create table t1(a int, b varchar(100));
    65  insert into t1 values(1 , "PowerSlave");
    66  insert into t1 values(2 , "Powerage");
    67  insert into t1 values( 3 , "Singing Down the Lane" );
    68  insert into t1 values(4 , "Ziltoid the Omniscient");
    69  insert into t1 values(5 , "Casualties of Cool");
    70  insert into t1 values( 6 , "Epicloud");
    71  insert into t1 values(7 , "Somewhere in Time");
    72  insert into t1 values(8 , "Piece of Mind");
    73  SELECT a, REGEXP_INSTR(b, 'i', 2, 2) from t1;
    74  drop table t1;
    75  
    76  create table t1(a int, b varchar(100));
    77  insert into t1 values(1 , "PowerSlave");
    78  insert into t1 values(2 , "Powerage");
    79  insert into t1 values( 3 , "Singing Down the Lane" );
    80  insert into t1 values(4 , "Ziltoid the Omniscient");
    81  insert into t1 values(5 , "Casualties of Cool");
    82  insert into t1 values( 6 , "Epicloud");
    83  insert into t1 values(7 , "Somewhere in Time");
    84  insert into t1 values(8 , "Piece of Mind");
    85  SELECT a, REGEXP_INSTR(b, 'i', 2, 2, 1) from t1;
    86  drop table t1;
    87  
    88  DROP TABLE IF EXISTS t;
    89  CREATE TABLE t(str1 VARCHAR(20), str2 CHAR(20));
    90  INSERT INTO t VALUES ('W * P', 'W + Z - O'), ('have has having', 'do does doing');
    91  INSERT INTO t VALUES ('XV*XZ', 'PP-ZZ-DXA'), ('aa bbb cc ddd', 'k ii lll oooo');
    92  SELECT REGEXP_INSTR(str1, '*'), REGEXP_INSTR(str2,'hav') FROM t;
    93  drop table t;
    94  
    95  create database big_data_test;
    96  use big_data_test;
    97  create table table_basic_for_load_100M(col1 varchar(100));
    98  insert into table_basic_for_load_100M values(',GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy');
    99  insert into table_basic_for_load_100M values('a');
   100  select col1 from table_basic_for_load_100M;
   101  select REGEXP_INSTR(',GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy', ',',1);
   102  select REGEXP_INSTR(',GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy', 'a',1);
   103  select REGEXP_INSTR(col1,',',1) from big_data_test.table_basic_for_load_100M;
   104  select REGEXP_INSTR(col1,'a',1) from big_data_test.table_basic_for_load_100M;
   105  select REGEXP_REPLACE(',GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy', ',','b',1);
   106  select REGEXP_REPLACE(',GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy', 'a','b',1);
   107  select REGEXP_REPLACE(col1,',','b',1) from big_data_test.table_basic_for_load_100M;
   108  select REGEXP_REPLACE(col1,'a','b',1) from big_data_test.table_basic_for_load_100M;
   109  select REGEXP_SUBSTR(',GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy', ',',1);
   110  select REGEXP_SUBSTR(',GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy', 'a',1);
   111  select REGEXP_SUBSTR(col1,',',1) from big_data_test.table_basic_for_load_100M;
   112  select REGEXP_SUBSTR(col1,'a',1) from big_data_test.table_basic_for_load_100M;
   113  drop database big_data_test;