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

     1  #SELECT, EXTREME VALUE
     2  
     3  SELECT MID('Quadratically',5);
     4  SELECT MID('foobarbar' FROM 4);
     5  SELECT MID('Quadratically',5,6);
     6  SELECT MID('Sakila', -3);
     7  SELECT MID('Sakila', -5, 3);
     8  SELECT MID('Sakila' FROM -4 FOR 2);
     9  SELECT MID('monty',5,1);
    10  SELECT MID('a',1,2);
    11  select MID('abcdefg',3,2);
    12  select MID('abcdefg',3,2);
    13  select MID('abcdefg',-3,2);
    14  select MID('abcdefg',-1,5);
    15  select MID('abcdefg',0,0);
    16  
    17  select MID('abcdefg',-1,-1);
    18  select MID('abcdefg',1,-1);
    19  select mid('hello', 2, -1);
    20  
    21  select mid('hello', -1, 1);
    22  select mid('hello', -2, 1);
    23  select mid('hello', -4294967295, 1);
    24  select mid('hello', 4294967295, 1);
    25  select mid('hello', -4294967296, 1);
    26  select mid('hello', 4294967296, 1);
    27  select mid('hello', -4294967297, 1);
    28  select mid('hello', 4294967297, 1);
    29  
    30  select mid('hello', -18446744073709551615, 1);
    31  select mid('hello', 18446744073709551615, 1);
    32  select mid('hello', -18446744073709551616, 1);
    33  select mid('hello', 18446744073709551616, 1);
    34  select mid('hello', -18446744073709551617, 1);
    35  select mid('hello', 18446744073709551617, 1);
    36  select mid('hello', 1, -1);
    37  select mid('hello', 1, -4294967295);
    38  select mid('hello', 1, 4294967295);
    39  select mid('hello', 1, -4294967296);
    40  select mid('hello', 1, 4294967296);
    41  select mid('hello', 1, -4294967297);
    42  
    43  select mid('hello', 1, 4294967297);
    44  
    45  select mid('hello', 1, -18446744073709551615);
    46  select mid('hello', 1, 18446744073709551615);
    47  select mid('hello', 1, -18446744073709551616);
    48  select mid('hello', 1, 18446744073709551616);
    49  select mid('hello', 1, -18446744073709551617);
    50  select mid('hello', 1, 18446744073709551617);
    51  select mid('hello', -1, -1);
    52  
    53  select mid('hello', -4294967295, -4294967295);
    54  select mid('hello', 4294967295, 4294967295);
    55  select mid('hello', -4294967296, -4294967296);
    56  select mid('hello', 4294967296, 4294967296);
    57  select mid('hello', -4294967297, -4294967297);
    58  select mid('hello', 4294967297, 4294967297);
    59  
    60  select mid('hello', -18446744073709551615, -18446744073709551615);
    61  select mid('hello', 18446744073709551615, 18446744073709551615);
    62  select mid('hello', -18446744073709551616, -18446744073709551616);
    63  select mid('hello', 18446744073709551616, 18446744073709551616);
    64  select mid('hello', -18446744073709551617, -18446744073709551617);
    65  select mid('hello', 18446744073709551617, 18446744073709551617);
    66  
    67  SELECT MID('Sakila' FROM -4 FOR 2);
    68  SELECT MID('foobarbar' FROM 4);
    69  
    70  #别名
    71  SELECT MID('w3resource',4,3);
    72  
    73  #嵌套
    74  select mid('abc', cast(2 as unsigned int));
    75  
    76  #数据类型
    77  create table t1(f1 varchar(255));
    78  insert into t1 values ("123"),("456");
    79  select mid(f1,1,1) from t1 group by 1;
    80  drop table t1;
    81  
    82  
    83  
    84  #EXTREME VALUE, IN操作符
    85  SELECT '1' IN ('1', MID(-9223372036854775809, 1));
    86  
    87  #嵌套
    88  SELECT MID('1', year(FROM_UNIXTIME(-1)));
    89  select reverse(mid(1,1,1));
    90  
    91  
    92  #DISTINCT
    93  CREATE TABLE t1(c1 CHAR(30));
    94  INSERT INTO t1 VALUES('111'),('222');
    95  SELECT DISTINCT mid(c1, 1, 2147483647) FROM t1;
    96  SELECT DISTINCT mid(c1, 1, 2147483648) FROM t1;
    97  SELECT DISTINCT mid(c1, -1, 2147483648) FROM t1;
    98  
    99  SELECT DISTINCT mid(c1, -2147483647, 2147483648) FROM t1;
   100  
   101  SELECT DISTINCT mid(c1, 9223372036854775807, 23) FROM t1;
   102  DROP TABLE t1;
   103  
   104  #嵌套, LONGTEXT,GROUP_CONCAT暂不支持
   105  #CREATE TABLE t1 (f1 LONGTEXT , f2  INTEGER);
   106  #INSERT INTO t1 VALUES (REPEAT('a', 500000), 0), (REPEAT('b', 500000), 1), (REPEAT('c', 500000), 2);
   107  #SELECT MID(GROUP_CONCAT(DISTINCT f1 ORDER BY f1 DESC), 1, 5) FROM t1;
   108  #SELECT MID(GROUP_CONCAT(DISTINCT UPPER(f1) ORDER BY f1), 1, 5) FROM t1;
   109  #DROP TABLE t1;
   110  
   111  #SELECT, 子查询
   112  create table t1 (b varchar(5));
   113  insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde');
   114  select *,mid(b,1),mid(b,-1),mid(b,-2),mid(b,-3),mid(b,-4),mid(b,-5) from t1;
   115  select * from (select *,mid(b,1),mid(b,-1),mid(b,-2),mid(b,-3),mid(b,-4),mid(b,-5) from t1) t;
   116  drop table t1;
   117  
   118  #SELECT, GROUP BY
   119  CREATE TABLE t1 (a varchar(2000), b varchar(10));
   120  INSERT INTO t1 select space(1300),'one';
   121  INSERT INTO t1 select space(1300),'two';
   122  
   123  -- @separator:table
   124  SELECT MID(a,1,10), LENGTH(a) FROM t1 GROUP BY a;
   125  drop table t1;
   126  CREATE TABLE t1(a INT);
   127  INSERT INTO t1 VALUES (0),(0);
   128  SELECT 1 FROM t1 GROUP BY MID('',1,'');
   129  drop table t1;
   130  
   131  #INSERT
   132  create table t1 (email varchar(50));
   133  insert into t1 values ('sasha@mysql.com'),('monty@mysql.com'),('foo@hotmail.com'),('foo@aol.com'),('bar@aol.com');
   134  create table t2(id int not null, t2 varchar(50));
   135  insert into t2 select 1, mid(email, 2,1) from t1;
   136  select id from t2;
   137  select t2 from t2;
   138  drop table t1;
   139  drop table t2;
   140  
   141  
   142  #字符集
   143  
   144  create table t1 (a varchar(50));
   145  insert into t1 values("你好"), ("再见"), ("今天");
   146  select * from t1;
   147  select mid(a, 1, 1) from t1;
   148  drop table t1;
   149  
   150  #NULL
   151  select mid(null, 1, 1);
   152  
   153  #WHERE 
   154  create table t1 (b varchar(5));
   155  insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde');
   156  select * from t1 where mid(b,1,1) = 'a';
   157  drop table t1;
   158  
   159  #HAVING & 逻辑运算
   160  drop table if exists t1;
   161  create table t1(b varchar(5));
   162  insert into t1 values('ab'), ('abc'), ('abcd'), ('abcde');
   163  select b from t1 group by b having mid(b,1,1)+'a'='aa';
   164  drop table t1;
   165  
   166  #ON CONDITION
   167  drop table if exists t1;
   168  drop table if exists t2;
   169  create table t1(a varchar(5));
   170  create table t2(a varchar(20));
   171  insert into t1 values('ab'), ('abc'), ('abcd'), ('abcde');
   172  insert into t2 values("2013-04-30"),("1994-10-04"),("2018-06-04"),("2012-10-12"), ("abc"),("abcd");
   173  SELECT t1.a, t2.a FROM t1 JOIN t2 ON (mid(t1.a, 1,1) = mid(t2.a, 1,1 ));
   174  drop table t1;
   175  drop table t2;
   176  
   177  #BLOB
   178  drop table if exists t1;
   179  create table t1(a blob);
   180  insert into t1 values('ab'), ('abc'), ('abcd'), ('abcde');
   181  SELECT MID(a,1,4), LENGTH(a) FROM t1 GROUP BY a;
   182  SELECT MID(a,-1) FROM t1;
   183  drop table t1;