github.com/matrixorigin/matrixone@v1.2.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; 166 create database big_data_test; 167 use big_data_test; 168 create table table_basic_for_load_100M(col1 varchar(100)); 169 insert into table_basic_for_load_100M values(',GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy'); 170 insert into table_basic_for_load_100M values('a'); 171 select col1 from table_basic_for_load_100M; 172 col1 173 ,GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy 174 a 175 select REGEXP_INSTR(',GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy', ',',1); 176 regexp_instr(,GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy, ,, 1) 177 1 178 select REGEXP_INSTR(',GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy', 'a',1); 179 regexp_instr(,GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy, a, 1) 180 31 181 select REGEXP_INSTR(col1,',',1) from big_data_test.table_basic_for_load_100M; 182 regexp_instr(col1, ,, 1) 183 1 184 0 185 select REGEXP_INSTR(col1,'a',1) from big_data_test.table_basic_for_load_100M; 186 regexp_instr(col1, a, 1) 187 31 188 1 189 select REGEXP_REPLACE(',GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy', ',','b',1); 190 regexp_replace(,GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy, ,, b, 1) 191 bGHIbG;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy 192 select REGEXP_REPLACE(',GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy', 'a','b',1); 193 regexp_replace(,GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy, a, b, 1) 194 ,GHI,G;KL/MN?OPQR.STU-_+=VWXYZbbcdefghigklmnopqrstuvwxy 195 select REGEXP_REPLACE(col1,',','b',1) from big_data_test.table_basic_for_load_100M; 196 regexp_replace(col1, ,, b, 1) 197 bGHIbG;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy 198 a 199 select REGEXP_REPLACE(col1,'a','b',1) from big_data_test.table_basic_for_load_100M; 200 regexp_replace(col1, a, b, 1) 201 ,GHI,G;KL/MN?OPQR.STU-_+=VWXYZbbcdefghigklmnopqrstuvwxy 202 b 203 select REGEXP_SUBSTR(',GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy', ',',1); 204 regexp_substr(,GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy, ,, 1) 205 , 206 select REGEXP_SUBSTR(',GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy', 'a',1); 207 regexp_substr(,GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxy, a, 1) 208 a 209 select REGEXP_SUBSTR(col1,',',1) from big_data_test.table_basic_for_load_100M; 210 regexp_substr(col1, ,, 1) 211 , 212 null 213 select REGEXP_SUBSTR(col1,'a',1) from big_data_test.table_basic_for_load_100M; 214 regexp_substr(col1, a, 1) 215 a 216 a 217 drop database big_data_test;