github.com/matrixorigin/matrixone@v1.2.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 Data truncation: data out of range: data type int64, value '-18446744073709551615' 75 select substring('hello', 18446744073709551615, 1); 76 Data truncation: data out of range: data type int64, value '18446744073709551615' 77 select substring('hello', -18446744073709551616, 1); 78 Data truncation: data out of range: data type int64, value '-18446744073709551616' 79 select substring('hello', 18446744073709551616, 1); 80 Data truncation: data out of range: data type int64, value '18446744073709551616' 81 select substring('hello', -18446744073709551617, 1); 82 Data truncation: data out of range: data type int64, value '-18446744073709551617' 83 select substring('hello', 18446744073709551617, 1); 84 Data truncation: data out of range: data type int64, value '18446744073709551617' 85 select substring('hello', 1, -1); 86 substring(hello, 1, -1) 87 88 select substring('hello', 1, -4294967295); 89 substring(hello, 1, -4294967295) 90 91 select substring('hello', 1, 4294967295); 92 substring(hello, 1, 4294967295) 93 hello 94 select substring('hello', 1, -4294967296); 95 substring(hello, 1, -4294967296) 96 97 select substring('hello', 1, 4294967296); 98 substring(hello, 1, 4294967296) 99 hello 100 select substring('hello', 1, -4294967297); 101 substring(hello, 1, -4294967297) 102 103 select substring('hello', 1, 4294967297); 104 substring(hello, 1, 4294967297) 105 hello 106 select substring('hello', 1, -18446744073709551615); 107 Data truncation: data out of range: data type int64, value '-18446744073709551615' 108 select substring('hello', 1, 18446744073709551615); 109 Data truncation: data out of range: data type int64, value '18446744073709551615' 110 select substring('hello', 1, -18446744073709551616); 111 Data truncation: data out of range: data type int64, value '-18446744073709551616' 112 select substring('hello', 1, 18446744073709551616); 113 Data truncation: data out of range: data type int64, value '18446744073709551616' 114 select substring('hello', 1, -18446744073709551617); 115 Data truncation: data out of range: data type int64, value '-18446744073709551617' 116 select substring('hello', 1, 18446744073709551617); 117 Data truncation: data out of range: data type int64, value '18446744073709551617' 118 select substring('hello', -1, -1); 119 substring(hello, -1, -1) 120 121 select substring('hello', -4294967295, -4294967295); 122 substring(hello, -4294967295, -4294967295) 123 124 select substring('hello', 4294967295, 4294967295); 125 substring(hello, 4294967295, 4294967295) 126 127 select substring('hello', -4294967296, -4294967296); 128 substring(hello, -4294967296, -4294967296) 129 130 select substring('hello', 4294967296, 4294967296); 131 substring(hello, 4294967296, 4294967296) 132 133 select substring('hello', -4294967297, -4294967297); 134 substring(hello, -4294967297, -4294967297) 135 136 select substring('hello', 4294967297, 4294967297); 137 substring(hello, 4294967297, 4294967297) 138 139 select substring('hello', -18446744073709551615, -18446744073709551615); 140 Data truncation: data out of range: data type int64, value '-18446744073709551615' 141 select substring('hello', 18446744073709551615, 18446744073709551615); 142 Data truncation: data out of range: data type int64, value '18446744073709551615' 143 select substring('hello', -18446744073709551616, -18446744073709551616); 144 Data truncation: data out of range: data type int64, value '-18446744073709551616' 145 select substring('hello', 18446744073709551616, 18446744073709551616); 146 Data truncation: data out of range: data type int64, value '18446744073709551616' 147 select substring('hello', -18446744073709551617, -18446744073709551617); 148 Data truncation: data out of range: data type int64, value '-18446744073709551617' 149 select substring('hello', 18446744073709551617, 18446744073709551617); 150 Data truncation: data out of range: data type int64, value '18446744073709551617' 151 SELECT SUBSTRING('Sakila' FROM -4 FOR 2); 152 substring(Sakila, -4, 2) 153 ki 154 SELECT SUBSTRING('foobarbar' FROM 4); 155 substring(foobarbar, 4) 156 barbar 157 SELECT SUBSTR('w3resource',4,3); 158 substr(w3resource, 4, 3) 159 eso 160 select substring('abc', cast(2 as unsigned int)); 161 substring(abc, cast(2 as int unsigned)) 162 bc 163 create table t1(f1 varchar(255)); 164 insert into t1 values ("123"),("456"); 165 select substring(f1,1,1) from t1 group by 1; 166 SQL syntax error: column "t1.f1" must appear in the GROUP BY clause or be used in an aggregate function 167 drop table t1; 168 SELECT '1' IN ('1', SUBSTRING(-9223372036854775809, 1)); 169 1 in (1, substring(-9223372036854775809, 1)) 170 true 171 SELECT SUBSTRING('1', year(FROM_UNIXTIME(-1))); 172 substring(1, year(from_unixtime(-1))) 173 null 174 select reverse(substring(1,1,1)); 175 reverse(substring(1, 1, 1)) 176 1 177 CREATE TABLE t1(c1 CHAR(30)); 178 INSERT INTO t1 VALUES('111'),('222'); 179 SELECT DISTINCT substr(c1, 1, 2147483647) FROM t1; 180 substr(c1, 1, 2147483647) 181 111 182 222 183 SELECT DISTINCT substr(c1, 1, 2147483648) FROM t1; 184 substr(c1, 1, 2147483648) 185 111 186 222 187 SELECT DISTINCT substr(c1, -1, 2147483648) FROM t1; 188 substr(c1, -1, 2147483648) 189 1 190 2 191 SELECT DISTINCT substr(c1, -2147483647, 2147483648) FROM t1; 192 substr(c1, -2147483647, 2147483648) 193 194 SELECT DISTINCT substr(c1, 9223372036854775807, 23) FROM t1; 195 substr(c1, 9223372036854775807, 23) 196 197 DROP TABLE t1; 198 create table t1 (b varchar(5)); 199 insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde'); 200 select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1; 201 b substring(b, 1) substring(b, -1) substring(b, -2) substring(b, -3) substring(b, -4) substring(b, -5) 202 ab ab b ab 203 abc abc c bc abc 204 abcd abcd d cd bcd abcd 205 abcde abcde e de cde bcde abcde 206 select * from (select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1) t; 207 b substring(b, 1) substring(b, -1) substring(b, -2) substring(b, -3) substring(b, -4) substring(b, -5) 208 ab ab b ab 209 abc abc c bc abc 210 abcd abcd d cd bcd abcd 211 abcde abcde e de cde bcde abcde 212 drop table t1; 213 CREATE TABLE t1 (a varchar(2000), b varchar(10)); 214 INSERT INTO t1 select space(1300),'one'; 215 INSERT INTO t1 select space(1300),'two'; 216 SELECT SUBSTRING(a,1,10), LENGTH(a) FROM t1 GROUP BY a; 217 substring(a, 1, 10) length(a) 218 1300 219 drop table t1; 220 CREATE TABLE t1(a INT); 221 INSERT INTO t1 VALUES (0),(0); 222 SELECT 1 FROM t1 GROUP BY SUBSTRING('',1,''); 223 invalid argument cast to int, bad value 224 drop table t1; 225 create table t1 (email varchar(50)); 226 insert into t1 values ('sasha@mysql.com'),('monty@mysql.com'),('foo@hotmail.com'),('foo@aol.com'),('bar@aol.com'); 227 create table t2(id int not null, t2 varchar(50)); 228 insert into t2 select 1, substring(email, 2,1) from t1; 229 select id from t2; 230 id 231 1 232 1 233 1 234 1 235 1 236 select t2 from t2; 237 t2 238 a 239 o 240 o 241 o 242 a 243 drop table t1; 244 drop table t2; 245 create table t1 (a varchar(50)); 246 insert into t1 values("你好"), ("再见"), ("今天"); 247 select * from t1; 248 a 249 你好 250 再见 251 今天 252 select substring(a, 1, 1) from t1; 253 substring(a, 1, 1) 254 你 255 再 256 今 257 drop table t1; 258 select substring(null, 1, 1); 259 substring(null, 1, 1) 260 null 261 create table t1 (b varchar(5)); 262 insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde'); 263 select * from t1 where substring(b,1,1) = 'a'; 264 b 265 ab 266 abc 267 abcd 268 abcde 269 drop table t1; 270 drop table if exists t1; 271 create table t1(b varchar(5)); 272 insert into t1 values('ab'), ('abc'), ('abcd'), ('abcde'); 273 select b from t1 group by b having substring(b,1,1)+'a'='aa'; 274 b 275 ab 276 abc 277 abcd 278 abcde 279 drop table t1; 280 drop table if exists t1; 281 drop table if exists t2; 282 create table t1(a varchar(5)); 283 create table t2(a varchar(20)); 284 insert into t1 values('ab'), ('abc'), ('abcd'), ('abcde'); 285 insert into t2 values("2013-04-30"),("1994-10-04"),("2018-06-04"),("2012-10-12"), ("abc"),("abcd"); 286 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (substring(t1.a, 1,1) = substring(t2.a, 1,1 )); 287 a a 288 ab abc 289 abc abc 290 abcd abc 291 abcde abc 292 ab abcd 293 abc abcd 294 abcd abcd 295 abcde abcd 296 drop table t1; 297 drop table t2; 298 drop table if exists t1; 299 create table t1(a blob); 300 insert into t1 values('ab'), ('abc'), ('abcd'), ('abcde'); 301 SELECT SUBSTRING(a,1,4), LENGTH(a) FROM t1 GROUP BY a; 302 substring(a, 1, 4) length(a) 303 ab 2 304 abc 3 305 abcd 4 306 abcd 5 307 SELECT SUBSTRING(a,-1) FROM t1; 308 substring(a, -1) 309 b 310 c 311 d 312 e 313 drop table t1;