github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_substr.sql (about) 1 -- @suite 2 -- @setup 3 drop table if exists t1; 4 create table t1 ( id int, c char(10),vc varchar(20)); 5 insert into t1 values (1,'Daffy','Aducklife'); 6 insert into t1 values (1,'Daffy','Aducklife'); 7 insert into t1 values (2,'Bugs','Arabbitlife'); 8 insert into t1 values (3,'Cowboy','Lifeontherange'); 9 insert into t1 values (4,'Anonymous','Wannabuythisbook?'); 10 insert into t1 values (5,'BestSeller','OneHeckuvabook'); 11 insert into t1 values (5,'BestSeller','OneHeckuvabook'); 12 insert into t1 values (6,'EveryoneBu','Thisverybook'); 13 insert into t1 values (7,'SanFran','Itisasanfranlifetyle'); 14 insert into t1 values (8,'BerkAuthor','Cool.Berkly.the.book'); 15 insert into t1 values (9,null,null); 16 -- insert into t1 values (10,'北京市','中关村'); 17 insert into t1 values (10,'',''); 18 19 -- @case 20 -- @desc:test for SUBSTRING 21 -- @label:bvt 22 select SUBSTRING(c,1),SUBSTR(vc,1) from t1; 23 select SUBSTRING(c,2),SUBSTR(vc,2) from t1; 24 select SUBSTRING(c,5),SUBSTR(vc,9) from t1 where id = 1 ; 25 select substring(c,11),substr(vc,13) from t1 where id = 6 ; 26 27 select SUBSTRING(c,-1),SUBSTR(vc,-1) from t1; 28 select SUBSTRING(c,-2),SUBSTR(vc,-2) from t1; 29 select SUBSTRING(c,-5),SUBSTR(vc,-9) from t1 where id = 1 ; 30 31 select substring(c,-11),substr(vc,-13) from t1 where id = 6 ; 32 33 select SUBSTRING(c,1,1),SUBSTR(vc,1,1) from t1 where id = 1 ; 34 select SUBSTRING(c,2,2),SUBSTR(vc,3,3) from t1 where id = 1 ; 35 select SUBSTRING(c,2,5),SUBSTR(vc,3,9) from t1 where id = 1 ; 36 select substring(c,2,9),substr(vc,3,17) from t1 where id = 4 ; 37 select substring(c,2,10),substr(vc,3,18) from t1 where id = 4 ; 38 select substring(c,5,4),substr(vc,5,4) from t1 where id = 4 ; 39 select substring(c,10,10),substr(vc,18,18) from t1 where id = 4 ; 40 select substring(c,2,-1),substr(vc,3,-1) from t1 where id = 4 ; 41 42 select SUBSTRING(c,-1,1),SUBSTR(vc,1,1) from t1 where id = 1 ; 43 select SUBSTRING(c,-2,2),SUBSTR(vc,3,3) from t1 where id = 1 ; 44 select SUBSTRING(c,-2,5),SUBSTR(vc,3,9) from t1 where id = 1 ; 45 select substring(c,-2,9),substr(vc,3,17) from t1 where id = 4 ; 46 select substring(c,-2,10),substr(vc,3,18) from t1 where id = 4 ; 47 select substring(c,1,-1),substr(vc,3,-2) from t1 where id = 4 ; 48 49 select substring(c,1,a),substr(vc,3,1) from t1 where id = 4 ; 50 select substring(c,1,2),substr(vc,a,1) from t1 where id = 4 ; 51 52 -- @case 53 -- @desc:test for SUBSTRING with max,min 54 -- @label:bvt 55 select max(substr(c,2)) from t1; 56 select min(substr(c,2)) from t1; 57 58 -- @case 59 -- @desc:test for SUBSTRING with max,min 60 -- @label:bvt 61 select * from t1 where substr(c,2) = 'affy'; 62 select * from t1 where substr(c,2) <> 'affy'; 63 select * from t1 where substr(c,2) > 'affy'; 64 select * from t1 where substr(c,2) > substring('fdasfsad',2); 65 66 -- @case 67 -- @desc:test for SUBSTRING with distinct 68 -- @label:bvt 69 select distinct(substr(c,2)) from t1 order by 1; 70 select distinct(substr(vc,3)) from t1 order by 1; 71 72 73 -- @case 74 -- @desc:test for endswith 75 -- @label:bvt 76 select endswith(c,'a'),endswith(vc,'a') from t1; 77 select endswith(c,'y'),endswith(vc,'e') from t1; 78 select * from t1 where endswith(c,'y'); 79 select * from t1 where endswith(c,'y') and endswith(vc,'ge'); 80 81 -- @case 82 -- @desc:test for startswith 83 -- @label:bvt 84 select startswith(c,'B'),startswith(vc,'A') from t1; 85 select startswith(c,'y'),startswith(vc,'e') from t1; 86 select * from t1 where startswith(c,'B'); 87 select * from t1 where startswith(c,'B') and startswith(vc,'A'); 88 89 -- @case 90 -- @desc:test for lpad 91 -- @label:bvt 92 select lpad(c,0,'*') from t1; 93 select lpad(c,1,'*') from t1; 94 select lpad(c,5,'*') from t1; 95 select lpad(c,10,'*') from t1; 96 97 select rpad(c,'1','*') from t1; 98 99 100 -- @case 101 -- @desc:test for rpad 102 -- @label:bvt 103 select rpad(c,0,'*') from t1; 104 select rpad(c,1,'*') from t1; 105 select rpad(c,5,'*') from t1; 106 select rpad(c,10,'*') from t1; 107 108 -- @bvt:issue#3165 109 select rpad(c,'1','*') from t1; 110 -- @bvt:issue 111 112 113 -- @suite 114 -- @setup 115 drop table if exists t1; 116 create table t1 ( id int, c char(20),vc varchar(50)); 117 insert into t1 values (1,'Daffy ',' Aducklife'); 118 insert into t1 values (1,' Daffy ','Aducklife '); 119 insert into t1 values (2,' Bugs',' Arabbitlife '); 120 insert into t1 values (3,' Cowboy',' Lifeontherange'); 121 insert into t1 values (4,' Anonymous ',' Wannabuythisbook? '); 122 insert into t1 values (5,' BestSeller',' OneHeckuvabook '); 123 insert into t1 values (5,' BestSeller','OneHeckuvabook '); 124 insert into t1 values (6,' EveryoneBu',' Thisverybook '); 125 insert into t1 values (7,' SanFran',' Itisasanfranlifetyle '); 126 insert into t1 values (8,' BerkAuthor',' Cool.Berkly.the.book '); 127 insert into t1 values (9,null,null); 128 -- insert into t1 values (10,' 北京市 ',' 中关村 '); 129 insert into t1 values (10,'',''); 130 131 -- @case 132 -- @desc:test for ltrim,rtrim 133 -- @label:bvt 134 -- @separator:table 135 select ltrim(c),ltrim(vc) from t1; 136 -- @separator:table 137 select rtrim(c),rtrim(vc) from t1; 138 -- @separator:table 139 select ltrim(rtrim(c)),rtrim(ltrim(vc)) from t1; 140 -- @separator:table 141 select * from t1 where ltrim(c) = 'BestSeller'; 142 -- @separator:table 143 select * from t1 where ltrim(c) = 'BestSeller' and rtrim(vc) = 'OneHeckuvabook'; 144 145 146 -- @case 147 -- @desc:test for space 148 -- @label:bvt 149 drop table if exists t1; 150 create table t1 ( d int); 151 insert into t1 values(0),(-1),(2),(10); 152 -- @separator:table 153 select space(d) from t1 where d <> -1; 154 -- @separator:table 155 select space(d) from t1; 156 drop table t1;