github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/operator/regexp_operator.test (about)

     1  SELECT 'Corn' REGEXP 'Corn' AS 'Corn';
     2  SELECT 'Acorn' REGEXP 'Corn' AS 'Acorn';
     3  SELECT 'Corner' REGEXP 'Corn' AS 'Corner';
     4  SELECT 'Cheese' REGEXP 'Corn' AS 'Cheese';
     5  
     6  SELECT 'Corn' REGEXP '^Co' AS 'Corn';
     7  SELECT 'Acorn' REGEXP '^Co' AS 'Acorn';
     8  SELECT 'Cheese' REGEXP '^Co' AS 'Cheese';
     9  
    10  SELECT 'Corn' REGEXP 'rn$' AS 'Corn';
    11  SELECT 'Acorn' REGEXP 'rn$' AS 'Acorn';
    12  SELECT 'Cheese' REGEXP 'rn$' AS 'Cheese'; 
    13  
    14  SELECT 'Corn' REGEXP '.' AS 'Corn';
    15  SELECT 'Cheese' REGEXP '.' AS 'Cheese';
    16  
    17  SELECT 'Corn' REGEXP '^C.rn$' AS 'Corn';
    18  SELECT 'Crn' REGEXP '^C.rn$' AS 'Crn';
    19  
    20  SELECT 'Tweet' REGEXP '^Tw..t$' AS 'Tweet'; 
    21  SELECT 'Tweat' REGEXP '^Tw..t$' AS 'Tweat'; 
    22  SELECT 'Tweet' REGEXP '^Tw.t$' AS 'Tweet';
    23  SELECT 'Twit' REGEXP '^Tw..t$' AS 'Twit';
    24  
    25  SELECT 'Tweet' REGEXP '^Tw.{2}t$' AS 'Tweet';
    26  SELECT 'Tweat' REGEXP '^Tw.{2}t$' AS 'Tweat';
    27  SELECT 'Tweet' REGEXP '^Tw.{1}t$' AS 'Tweet';
    28  SELECT 'Twit' REGEXP '^Tw.{2}t$' AS 'Twit';
    29  
    30  SELECT 'Twet' REGEXP '^Twe*t$' AS 'Twet';
    31  SELECT 'Tweet' REGEXP '^Twe*t$' AS 'Tweet';
    32  SELECT 'Tweeet' REGEXP '^Twe*t$' AS 'Tweeet';
    33  SELECT 'Twt' REGEXP '^Twe*t$' AS 'Twt';
    34  SELECT 'Twit' REGEXP '^Twe*t$' AS 'Twit';
    35  SELECT 'Twiet' REGEXP '^Twe*t$' AS 'Twiet';
    36  SELECT 'Tweit' REGEXP '^Twe*t$' AS 'Tweit';
    37  
    38  SELECT 'Twet' REGEXP '^Twe+t$' AS 'Twet';
    39  SELECT 'Tweet' REGEXP '^Twe+t$' AS 'Tweet';
    40  SELECT 'Tweeet' REGEXP '^Twe+t$' AS 'Tweeet';
    41  SELECT 'Twt' REGEXP '^Twe+t$' AS 'Twt';
    42  SELECT 'Twit' REGEXP '^Twe+t$' AS 'Twit';
    43  SELECT 'Twiet' REGEXP '^Twe+t$' AS 'Twiet';
    44  SELECT 'Tweit' REGEXP '^Twe+t$' AS 'Tweit';
    45  
    46  SELECT 'Tweet' REGEXP 'Tw|et' AS 'Tweet';
    47  SELECT 'For Let' REGEXP 'Tw|et' AS 'For Let';
    48  SELECT 'Banana' REGEXP 'Tw|et' AS 'Banana';
    49  
    50  SELECT 'Cat' REGEXP 'Cat|Dog' AS 'Cat';
    51  SELECT 'Dog' REGEXP 'Cat|Dog' AS 'Dog';
    52  SELECT 'Doggone' REGEXP 'Cat|Dog' AS 'Doggone';
    53  SELECT 'Banana' REGEXP 'Cat|Dog' AS 'Banana';
    54  
    55  SELECT 'Banana' REGEXP '(an)*' AS 'Banana';
    56  SELECT 'Land' REGEXP '(an)*' AS 'Land';
    57  SELECT 'Cheese' REGEXP '(an)*' AS 'Cheese';
    58  
    59  SELECT 'Banana' REGEXP '^B(an)*d$' AS 'Banana';
    60  SELECT 'Band' REGEXP '^B(an)*d$' AS 'Band';
    61  SELECT 'Bald' REGEXP '^B(an)*d$' AS 'Bald';
    62  SELECT 'Bad' REGEXP '^B(an)*d$' AS 'Bad';
    63  
    64  SELECT 'Tweeet' REGEXP 'e{3}' AS 'Tweeet';
    65  SELECT 'Tweet' REGEXP 'e{3}' AS 'Tweet';
    66  
    67  SELECT 'Tweet 123' REGEXP '[0-9]' AS 'Tweet123';
    68  SELECT 'Tweet ABC' REGEXP '[0-9]' AS 'TweetABC';
    69  
    70  
    71  SELECT 'Tweet 123' REGEXP '[A-Z]' AS 'Tweet123';
    72  SELECT 'ABC' REGEXP '[A-Z]' AS 'ABC';
    73  SELECT '123' REGEXP '[A-Z]' AS '123';
    74  
    75  SELECT '123' REGEXP '[1-3]' AS '123';
    76  SELECT '012' REGEXP '[1-3]' AS '012';
    77  SELECT '045' REGEXP '[1-3]' AS '045';
    78  
    79  SELECT '123' REGEXP '[^1-3]' AS '123';
    80  SELECT '012' REGEXP '[^1-3]' AS '012';
    81  SELECT '045' REGEXP '[^1-3]' AS '045';
    82  
    83  create table t1(a int, b varchar(100));
    84  insert into t1 values(1 , "PowerSlave");
    85  insert into t1 values(2 , "Powerage");
    86  insert into t1 values( 3 , "Singing Down the Lane" );
    87  insert into t1 values(4 , "Ziltoid the Omniscient");
    88  insert into t1 values(5 , "Casualties of Cool");
    89  insert into t1 values( 6 , "Epicloud");
    90  insert into t1 values(7 , "Somewhere in Time");
    91  insert into t1 values(8 , "Piece of Mind");
    92  insert into t1 values( 9 , "Killers");
    93  insert into t1 values(10 , "No Prayer for the Dying");
    94  insert into t1 values(11 , "No Sound Without Silence");
    95  insert into t1 values(12 , "Big Swing Face");
    96  insert into t1 values(13 , "Blue Night");
    97  insert into t1 values(14 , "Eternity");
    98  insert into t1 values(15 , "Scandinavia");
    99  insert into t1 values(16 , "Long Lost Suitcase");
   100  insert into t1 values(17 , "Praise and Blame");
   101  insert into t1 values(18 , "Along Came Jones");
   102  insert into t1 values(19 , "All Night Wrong");
   103  insert into t1 values(20 , "The Sixteen Men of Tain");
   104  SELECT a, b
   105  FROM t1
   106  WHERE b REGEXP '^Power';
   107  drop table t1;