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

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