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

     1  SELECT REGEXP_INSTR('Cat', 'at') Result;
     2  result
     3  2
     4  SELECT REGEXP_INSTR('Cat', '^at') Result;
     5  result
     6  0
     7  SELECT REGEXP_INSTR('at', '^at') Result;
     8  result
     9  1
    10  SELECT REGEXP_INSTR('Cat Cat', 'Cat', 2) Result;
    11  result
    12  5
    13  SELECT REGEXP_INSTR('Cat Cat', 'Cat', 2) AS 'Pos2';
    14  Pos2
    15  5
    16  SELECT REGEXP_INSTR('Cat Cat', 'Cat', 3) AS 'Pos3';
    17  Pos3
    18  5
    19  SELECT REGEXP_INSTR('Cat Cat', 'Cat', 5) AS 'Pos5';
    20  Pos5
    21  5
    22  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1) 'Pos1';
    23  Pos1
    24  1
    25  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2) 'Pos2';
    26  Pos2
    27  5
    28  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 6) 'Pos6';
    29  Pos6
    30  16
    31  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1) 'Occurrence1';
    32  Occurrence1
    33  1
    34  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 2) 'Occurrence2';
    35  Occurrence2
    36  5
    37  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 3) 'Occurrence3';
    38  Occurrence3
    39  16
    40  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2, 1) 'Occurrence1';
    41  Occurrence1
    42  5
    43  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2, 2) 'Occurrence2';
    44  Occurrence2
    45  16
    46  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2, 3) 'Occurrence3';
    47  Occurrence3
    48  0
    49  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 0) 'Option0';
    50  Option0
    51  1
    52  SELECT REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 1) 'Option1';
    53  Option1
    54  4
    55  SELECT REGEXP_INSTR('Cat', null) Result;
    56  result
    57  NULL
    58  SELECT REGEXP_INSTR(null, 'C.t') Result;
    59  result
    60  NULL
    61  SELECT REGEXP_INSTR(null,  null) Result;
    62  result
    63  null
    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, '^P') from t1;
    74  a    regexp_instr(b, ^P)
    75  1    1
    76  2    1
    77  3    0
    78  4    0
    79  5    0
    80  6    0
    81  7    0
    82  8    1
    83  drop table t1;
    84  create table t1(a int, b varchar(100));
    85  insert into t1 values(1 , "PowerSlave");
    86  insert into t1 values(2 , "Powerage");
    87  insert into t1 values( 3 , "Singing Down the Lane" );
    88  insert into t1 values(4 , "Ziltoid the Omniscient");
    89  insert into t1 values(5 , "Casualties of Cool");
    90  insert into t1 values( 6 , "Epicloud");
    91  insert into t1 values(7 , "Somewhere in Time");
    92  insert into t1 values(8 , "Piece of Mind");
    93  SELECT a, b from t1 WHERE REGEXP_INSTR(b, '^P') = 1;
    94  a    b
    95  1    PowerSlave
    96  2    Powerage
    97  8    Piece of Mind
    98  drop table t1;
    99  create table t1(a int, b varchar(100));
   100  insert into t1 values(1 , "PowerSlave");
   101  insert into t1 values(2 , "Powerage");
   102  insert into t1 values( 3 , "Singing Down the Lane" );
   103  insert into t1 values(4 , "Ziltoid the Omniscient");
   104  insert into t1 values(5 , "Casualties of Cool");
   105  insert into t1 values( 6 , "Epicloud");
   106  insert into t1 values(7 , "Somewhere in Time");
   107  insert into t1 values(8 , "Piece of Mind");
   108  SELECT a, REGEXP_INSTR(b, 'i', 3) from t1;
   109  a    regexp_instr(b, i, 3)
   110  1    0
   111  2    0
   112  3    5
   113  4    6
   114  5    8
   115  6    3
   116  7    11
   117  8    11
   118  drop table t1;
   119  create table t1(a int, b varchar(100));
   120  insert into t1 values(1 , "PowerSlave");
   121  insert into t1 values(2 , "Powerage");
   122  insert into t1 values( 3 , "Singing Down the Lane" );
   123  insert into t1 values(4 , "Ziltoid the Omniscient");
   124  insert into t1 values(5 , "Casualties of Cool");
   125  insert into t1 values( 6 , "Epicloud");
   126  insert into t1 values(7 , "Somewhere in Time");
   127  insert into t1 values(8 , "Piece of Mind");
   128  SELECT a, REGEXP_INSTR(b, 'i', 2, 2) from t1;
   129  a    regexp_instr(b, i, 2, 2)
   130  1    0
   131  2    0
   132  3    5
   133  4    6
   134  5    0
   135  6    0
   136  7    15
   137  8    11
   138  drop table t1;
   139  create table t1(a int, b varchar(100));
   140  insert into t1 values(1 , "PowerSlave");
   141  insert into t1 values(2 , "Powerage");
   142  insert into t1 values( 3 , "Singing Down the Lane" );
   143  insert into t1 values(4 , "Ziltoid the Omniscient");
   144  insert into t1 values(5 , "Casualties of Cool");
   145  insert into t1 values( 6 , "Epicloud");
   146  insert into t1 values(7 , "Somewhere in Time");
   147  insert into t1 values(8 , "Piece of Mind");
   148  SELECT a, REGEXP_INSTR(b, 'i', 2, 2, 1) from t1;
   149  a    regexp_instr(b, i, 2, 2, 1)
   150  1    0
   151  2    0
   152  3    6
   153  4    7
   154  5    0
   155  6    0
   156  7    16
   157  8    12
   158  drop table t1;
   159  DROP TABLE IF EXISTS t;
   160  CREATE TABLE t(str1 VARCHAR(20), str2 CHAR(20));
   161  INSERT INTO t VALUES ('W * P', 'W + Z - O'), ('have has having', 'do does doing');
   162  INSERT INTO t VALUES ('XV*XZ', 'PP-ZZ-DXA'), ('aa bbb cc ddd', 'k ii lll oooo');
   163  SELECT REGEXP_INSTR(str1, '*'), REGEXP_INSTR(str2,'hav') FROM t;
   164  invalid argument regexp_instr have invalid regexp pattern arg, bad value [*]
   165  drop table t;