github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_string_substring.result (about) 1 SELECT SUBSTRING('Quadratically',5); 2 substring(Quadratically, 5) 3 ratically 4 SELECT SUBSTRING('foobarbar' FROM 4); 5 substring(foobarbar, 4) 6 barbar 7 SELECT SUBSTRING('Quadratically',5,6); 8 substring(Quadratically, 5, 6) 9 ratica 10 SELECT SUBSTRING('Sakila', -3); 11 substring(Sakila, -3) 12 ila 13 SELECT SUBSTRING('Sakila', -5, 3); 14 substring(Sakila, -5, 3) 15 aki 16 SELECT SUBSTRING('Sakila' FROM -4 FOR 2); 17 substring(Sakila, -4, 2) 18 ki 19 SELECT SUBSTRING('monty',5,1); 20 substring(monty, 5, 1) 21 y 22 SELECT SUBSTRING('a',1,2); 23 substring(a, 1, 2) 24 a 25 select SUBSTR('abcdefg',3,2); 26 substr(abcdefg, 3, 2) 27 cd 28 select SUBSTRING('abcdefg',3,2); 29 substring(abcdefg, 3, 2) 30 cd 31 select SUBSTR('abcdefg',-3,2); 32 substr(abcdefg, -3, 2) 33 ef 34 select SUBSTR('abcdefg',-1,5); 35 substr(abcdefg, -1, 5) 36 g 37 select SUBSTR('abcdefg',0,0); 38 substr(abcdefg, 0, 0) 39 40 select SUBSTR('abcdefg',-1,-1); 41 substr(abcdefg, -1, -1) 42 43 select SUBSTR('abcdefg',1,-1); 44 substr(abcdefg, 1, -1) 45 46 select substring('hello', 2, -1); 47 substring(hello, 2, -1) 48 49 select substring('hello', -1, 1); 50 substring(hello, -1, 1) 51 o 52 select substring('hello', -2, 1); 53 substring(hello, -2, 1) 54 l 55 select substring('hello', -4294967295, 1); 56 substring(hello, -4294967295, 1) 57 58 select substring('hello', 4294967295, 1); 59 substring(hello, 4294967295, 1) 60 61 select substring('hello', -4294967296, 1); 62 substring(hello, -4294967296, 1) 63 64 select substring('hello', 4294967296, 1); 65 substring(hello, 4294967296, 1) 66 67 select substring('hello', -4294967297, 1); 68 substring(hello, -4294967297, 1) 69 70 select substring('hello', 4294967297, 1); 71 substring(hello, 4294967297, 1) 72 73 select substring('hello', -18446744073709551615, 1); 74 substring(hello, -18446744073709551615, 1) 75 76 select substring('hello', 18446744073709551615, 1); 77 invalid argument function substring(str, start, lenth), bad value 18446744073709551615 78 select substring('hello', -18446744073709551616, 1); 79 substring(hello, -18446744073709551616, 1) 80 81 select substring('hello', 18446744073709551616, 1); 82 invalid argument function substring(str, start, lenth), bad value 1.8446744073709552e+19 83 select substring('hello', -18446744073709551617, 1); 84 substring(hello, -18446744073709551617, 1) 85 86 select substring('hello', 18446744073709551617, 1); 87 invalid argument function substring(str, start, lenth), bad value 1.8446744073709552e+19 88 select substring('hello', 1, -1); 89 substring(hello, 1, -1) 90 91 select substring('hello', 1, -4294967295); 92 substring(hello, 1, -4294967295) 93 94 select substring('hello', 1, 4294967295); 95 substring(hello, 1, 4294967295) 96 hello 97 select substring('hello', 1, -4294967296); 98 substring(hello, 1, -4294967296) 99 100 select substring('hello', 1, 4294967296); 101 substring(hello, 1, 4294967296) 102 hello 103 select substring('hello', 1, -4294967297); 104 substring(hello, 1, -4294967297) 105 106 select substring('hello', 1, 4294967297); 107 substring(hello, 1, 4294967297) 108 hello 109 select substring('hello', 1, -18446744073709551615); 110 substring(hello, 1, -18446744073709551615) 111 112 select substring('hello', 1, 18446744073709551615); 113 invalid argument function substring(str, start, lenth), bad value 18446744073709551615 114 select substring('hello', 1, -18446744073709551616); 115 substring(hello, 1, -18446744073709551616) 116 117 select substring('hello', 1, 18446744073709551616); 118 invalid argument function substring(str, start, lenth), bad value 1.8446744073709552e+19 119 select substring('hello', 1, -18446744073709551617); 120 substring(hello, 1, -18446744073709551617) 121 122 select substring('hello', 1, 18446744073709551617); 123 invalid argument function substring(str, start, lenth), bad value 1.8446744073709552e+19 124 select substring('hello', -1, -1); 125 substring(hello, -1, -1) 126 127 select substring('hello', -4294967295, -4294967295); 128 substring(hello, -4294967295, -4294967295) 129 130 select substring('hello', 4294967295, 4294967295); 131 substring(hello, 4294967295, 4294967295) 132 133 select substring('hello', -4294967296, -4294967296); 134 substring(hello, -4294967296, -4294967296) 135 136 select substring('hello', 4294967296, 4294967296); 137 substring(hello, 4294967296, 4294967296) 138 139 select substring('hello', -4294967297, -4294967297); 140 substring(hello, -4294967297, -4294967297) 141 142 select substring('hello', 4294967297, 4294967297); 143 substring(hello, 4294967297, 4294967297) 144 145 select substring('hello', -18446744073709551615, -18446744073709551615); 146 substring(hello, -18446744073709551615, -18446744073709551615) 147 148 select substring('hello', 18446744073709551615, 18446744073709551615); 149 invalid argument function substring(str, start, lenth), bad value 18446744073709551615 150 select substring('hello', -18446744073709551616, -18446744073709551616); 151 substring(hello, -18446744073709551616, -18446744073709551616) 152 153 select substring('hello', 18446744073709551616, 18446744073709551616); 154 invalid argument function substring(str, start, lenth), bad value 1.8446744073709552e+19 155 select substring('hello', -18446744073709551617, -18446744073709551617); 156 substring(hello, -18446744073709551617, -18446744073709551617) 157 158 select substring('hello', 18446744073709551617, 18446744073709551617); 159 invalid argument function substring(str, start, lenth), bad value 1.8446744073709552e+19 160 SELECT SUBSTRING('Sakila' FROM -4 FOR 2); 161 substring(Sakila, -4, 2) 162 ki 163 SELECT SUBSTRING('foobarbar' FROM 4); 164 substring(foobarbar, 4) 165 barbar 166 SELECT SUBSTR('w3resource',4,3); 167 substr(w3resource, 4, 3) 168 eso 169 select substring('abc', cast(2 as unsigned int)); 170 substring(abc, cast(2 as int unsigned)) 171 bc 172 create table t1(f1 varchar(255)); 173 insert into t1 values ("123"),("456"); 174 select substring(f1,1,1) from t1 group by 1; 175 SQL syntax error: column "t1.f1" must appear in the GROUP BY clause or be used in an aggregate function 176 drop table t1; 177 SELECT '1' IN ('1', SUBSTRING(-9223372036854775809, 1)); 178 1 in (1, substring(-9223372036854775809, 1)) 179 true 180 SELECT SUBSTRING('1', year(FROM_UNIXTIME(-1))); 181 substring(1, year(from_unixtime(-1))) 182 null 183 select reverse(substring(1,1,1)); 184 reverse(substring(1, 1, 1)) 185 1 186 CREATE TABLE t1(c1 CHAR(30)); 187 INSERT INTO t1 VALUES('111'),('222'); 188 SELECT DISTINCT substr(c1, 1, 2147483647) FROM t1; 189 substr(c1, 1, 2147483647) 190 111 191 222 192 SELECT DISTINCT substr(c1, 1, 2147483648) FROM t1; 193 substr(c1, 1, 2147483648) 194 111 195 222 196 SELECT DISTINCT substr(c1, -1, 2147483648) FROM t1; 197 substr(c1, -1, 2147483648) 198 1 199 2 200 SELECT DISTINCT substr(c1, -2147483647, 2147483648) FROM t1; 201 substr(c1, -2147483647, 2147483648) 202 203 SELECT DISTINCT substr(c1, 9223372036854775807, 23) FROM t1; 204 substr(c1, 9223372036854775807, 23) 205 206 DROP TABLE t1; 207 create table t1 (b varchar(5)); 208 insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde'); 209 select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1; 210 b substring(b, 1) substring(b, -1) substring(b, -2) substring(b, -3) substring(b, -4) substring(b, -5) 211 ab ab b ab 212 abc abc c bc abc 213 abcd abcd d cd bcd abcd 214 abcde abcde e de cde bcde abcde 215 select * from (select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1) t; 216 b substring(b, 1) substring(b, -1) substring(b, -2) substring(b, -3) substring(b, -4) substring(b, -5) 217 ab ab b ab 218 abc abc c bc abc 219 abcd abcd d cd bcd abcd 220 abcde abcde e de cde bcde abcde 221 drop table t1; 222 CREATE TABLE t1 (a varchar(2000), b varchar(10)); 223 INSERT INTO t1 select space(1300),'one'; 224 INSERT INTO t1 select space(1300),'two'; 225 SELECT SUBSTRING(a,1,10), LENGTH(a) FROM t1 GROUP BY a; 226 substring(a, 1, 10) length(a) 227 1300 228 drop table t1; 229 CREATE TABLE t1(a INT); 230 INSERT INTO t1 VALUES (0),(0); 231 SELECT 1 FROM t1 GROUP BY SUBSTRING('',1,''); 232 invalid argument cast to int, bad value 233 drop table t1; 234 create table t1 (email varchar(50)); 235 insert into t1 values ('sasha@mysql.com'),('monty@mysql.com'),('foo@hotmail.com'),('foo@aol.com'),('bar@aol.com'); 236 create table t2(id int not null, t2 varchar(50)); 237 insert into t2 select 1, substring(email, 2,1) from t1; 238 select id from t2; 239 id 240 1 241 1 242 1 243 1 244 1 245 select t2 from t2; 246 t2 247 a 248 o 249 o 250 o 251 a 252 drop table t1; 253 drop table t2; 254 create table t1 (a varchar(50)); 255 insert into t1 values("你好"), ("再见"), ("今天"); 256 select * from t1; 257 a 258 你好 259 再见 260 今天 261 select substring(a, 1, 1) from t1; 262 substring(a, 1, 1) 263 你 264 再 265 今 266 drop table t1; 267 select substring(null, 1, 1); 268 substring(null, 1, 1) 269 null 270 create table t1 (b varchar(5)); 271 insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde'); 272 select * from t1 where substring(b,1,1) = 'a'; 273 b 274 ab 275 abc 276 abcd 277 abcde 278 drop table t1; 279 drop table if exists t1; 280 create table t1(b varchar(5)); 281 insert into t1 values('ab'), ('abc'), ('abcd'), ('abcde'); 282 select b from t1 group by b having substring(b,1,1)+'a'='aa'; 283 b 284 ab 285 abc 286 abcd 287 abcde 288 drop table t1; 289 drop table if exists t1; 290 drop table if exists t2; 291 create table t1(a varchar(5)); 292 create table t2(a varchar(20)); 293 insert into t1 values('ab'), ('abc'), ('abcd'), ('abcde'); 294 insert into t2 values("2013-04-30"),("1994-10-04"),("2018-06-04"),("2012-10-12"), ("abc"),("abcd"); 295 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (substring(t1.a, 1,1) = substring(t2.a, 1,1 )); 296 a a 297 ab abc 298 abc abc 299 abcd abc 300 abcde abc 301 ab abcd 302 abc abcd 303 abcd abcd 304 abcde abcd 305 drop table t1; 306 drop table t2; 307 drop table if exists t1; 308 create table t1(a blob); 309 insert into t1 values('ab'), ('abc'), ('abcd'), ('abcde'); 310 SELECT SUBSTRING(a,1,4), LENGTH(a) FROM t1 GROUP BY a; 311 substring(a, 1, 4) length(a) 312 ab 2 313 abc 3 314 abcd 4 315 abcd 5 316 SELECT SUBSTRING(a,-1) FROM t1; 317 substring(a, -1) 318 b 319 c 320 d 321 e 322 drop table t1;