github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_string_left.result (about) 1 select left('abcde', 3) from dual; 2 left(abcde, 3) 3 abc 4 select left('abcde', 0) from dual; 5 left(abcde, 0) 6 7 select left('abcde', 10) from dual; 8 left(abcde, 10) 9 abcde 10 select left('abcde', -1) from dual; 11 left(abcde, -1) 12 13 select left('abcde', null) from dual; 14 left(abcde, null) 15 null 16 select left(null, 3) from dual; 17 left(null, 3) 18 null 19 select left(null, null) from dual; 20 left(null, null) 21 null 22 select left('foobarbar', 5) from dual; 23 left(foobarbar, 5) 24 fooba 25 select left('qwerty', 1.2) from dual; 26 left(qwerty, 1.2) 27 q 28 select left('qwerty', 1.5) from dual; 29 left(qwerty, 1.5) 30 qw 31 select left('qwerty', 1.8) from dual; 32 left(qwerty, 1.8) 33 qw 34 select left("是都方式快递费",3) from dual; 35 left(是都方式快递费, 3) 36 是都方 37 select left("あいうえお",3) from dual; 38 left(あいうえお, 3) 39 あいう 40 select left("あいうえお ",3) from dual; 41 left(あいうえお , 3) 42 あいう 43 select left("あいうえお ",3) from dual; 44 left(あいうえお , 3) 45 あいう 46 select left("あいうえお ",3) from dual; 47 left(あいうえお , 3) 48 あいう 49 select left("龔龖龗龞龡",3) from dual; 50 left(龔龖龗龞龡, 3) 51 龔龖龗 52 select left("龔龖龗龞龡 ",3) from dual; 53 left(龔龖龗龞龡 , 3) 54 龔龖龗 55 select left("龔龖龗龞龡 ",3) from dual; 56 left(龔龖龗龞龡 , 3) 57 龔龖龗 58 select left("龔龖龗龞龡 ",3) from dual; 59 left(龔龖龗龞龡 , 3) 60 龔龖龗 61 drop table if exists t1; 62 CREATE TABLE t1 (str VARCHAR(100) NOT NULL, len INT); 63 insert into t1 values('abcdefghijklmn',3); 64 insert into t1 values(' ABCDEFGH123456', 3); 65 insert into t1 values('ABCDEF GHIJKLMN', 20); 66 insert into t1 values('ABCDEFGHijklmn ', -1); 67 insert into t1 values('ABCDEFGH123456', -35627164); 68 insert into t1 values('', 3); 69 select left(str, len) from t1; 70 left(str, len) 71 abc 72 A 73 ABCDEF GHIJKLMN 74 75 76 77 SELECT * from t1 where left(str, cos(0) + 3) = 'ABC' and len > 3; 78 str len 79 select left(str, 3) from t1; 80 left(str, 3) 81 abc 82 A 83 ABC 84 ABC 85 ABC 86 87 select left('sdfsdfsdfsdf', len) from t1; 88 left(sdfsdfsdfsdf, len) 89 sdf 90 sdf 91 sdfsdfsdfsdf 92 93 94 sdf 95 select left(NULL, TAN(45)) FROM t1; 96 left(null, tan(45)) 97 null 98 null 99 null 100 null 101 null 102 null 103 select left('str', COS(0) + TAN(45)) from t1 where len between 6 AND 21; 104 left(str, cos(0) + tan(45)) 105 str 106 SELECT left(str, -2) from t1 where len%3 = 1; 107 left(str, -2) 108 drop table t1; 109 DROP TABLE IF EXISTS t; 110 CREATE table t(age INT, name CHAR(20), address VARCHAR(30)); 111 INSERT INTO t VALUES(20,'ejifwvewv','shanghaishi1032long'); 112 INSERT INTO t VALUES(30,'zhangzianjd','minhangqulongminglu'); 113 INSERT INTO t VALUES(3627832,'hcdusanjfds','xuhuiqudadao'); 114 INSERT INTO t VALUES(3782,'ehuwqhd3283&*^','ehiw3232$'); 115 INSERT INTO t VALUES(42,'','nkej32'); 116 INSERT INTO t VALUES(-2281928939,'wlll',''); 117 Data truncation: data out of range: data type int32, value '-2281928939' 118 INSERT INTO t VALUES(-2281928939,'wlll',''); 119 Data truncation: data out of range: data type int32, value '-2281928939' 120 SELECT left(age, NULL) from t; 121 left(age, null) 122 null 123 null 124 null 125 null 126 null 127 SELECT left(age, 0) from t; 128 left(age, 0) 129 130 131 132 133 134 SELECT left(age, 2),left(name,5),left(address,10) from t; 135 left(age, 2) left(name, 5) left(address, 10) 136 20 ejifw shanghaish 137 30 zhang minhangqul 138 36 hcdus xuhuiqudad 139 37 ehuwq ehiw3232$ 140 42 nkej32 141 SELECT left(age,'1'),left(name,'2'), left(address, '3') from t; 142 left(age, 1) left(name, 2) left(address, 3) 143 2 ej sha 144 3 zh min 145 3 hc xuh 146 3 eh ehi 147 4 nke 148 SELECT left(age, COS(0)),left(name, sin(90) + 1),left(address, TAN(45) + 3) from t; 149 left(age, cos(0)) left(name, sin(90) + 1) left(address, tan(45) + 3) 150 2 ej shang 151 3 zh minha 152 3 hc xuhui 153 3 eh ehiw3 154 4 nkej3 155 SELECT left(age, length(name) / 2),left(name,1) from t; 156 left(age, length(name) / 2) left(name, 1) 157 20 e 158 30 z 159 362783 h 160 3782 e 161 162 SELECT left(name, 3) from t where age >= 20; 163 left(name, 3) 164 eji 165 zha 166 hcd 167 ehu 168 169 SELECT left(age, -4) from t where LENGTH(address) >= 20; 170 left(age, -4) 171 SELECT left(age, 2) from t where name LIKE 'hcdusanjfds'; 172 left(age, 2) 173 36 174 SELECT left(age, 2), address from t where name LIKE '%vewv'; 175 left(age, 2) address 176 20 shanghaishi1032long 177 SELECT address from t where left(name, 3) = 'eji'; 178 address 179 shanghaishi1032long 180 DROP TABLE IF EXISTS t; 181 CREATE TABLE t(id INT,dd1 DATE, dd2 DATETIME, dd3 TIMESTAMP, PRIMARY KEY (id)); 182 INSERT INTO t VALUES (1, '2020-01-01', '2020-01-01 12:12:12', '2020-02-02 06:06:06.163'); 183 INSERT INTO t VALUES (2, '2021-11-11', '2021-01-11 23:23:23', '2021-12-12 16:16:16.843'); 184 INSERT INTO t VALUES (3, '2002-11-11', '2002-01-11 23:23:23', '2002-12-12 16:16:16.843'); 185 SELECT left(dd1, length(dd2)) FROM t; 186 left(dd1, length(dd2)) 187 2020-01-01 188 2021-11-11 189 2002-11-11 190 SELECT left(dd1, TAN(45) + 6) FROM t; 191 left(dd1, tan(45) + 6) 192 2020-01- 193 2021-11- 194 2002-11- 195 SELECT left(dd1,COS(10) + 1) FROM t; 196 left(dd1, cos(10) + 1) 197 198 199 200 SELECT left(dd1,NULL) FROM t; 201 left(dd1, null) 202 null 203 null 204 null 205 SELECT left(NULL, -2) FROM t; 206 left(null, -2) 207 null 208 null 209 null 210 SELECT left(dd3, 4) FROM t WHERE dd1 LIKE '0001-01-01'; 211 left(dd3, 4) 212 SELECT * from t WHERE left(dd1, 2) = 20; 213 id dd1 dd2 dd3 214 1 2020-01-01 2020-01-01 12:12:12 2020-02-02 06:06:06 215 2 2021-11-11 2021-01-11 23:23:23 2021-12-12 16:16:17 216 3 2002-11-11 2002-01-11 23:23:23 2002-12-12 16:16:17 217 SELECT * from t WHERE MONTH(dd1) = 11 AND left(dd1, 4) = 2021; 218 id dd1 dd2 dd3 219 2 2021-11-11 2021-01-11 23:23:23 2021-12-12 16:16:17 220 DROP TABLE IF EXISTS t; 221 CREATE TABLE t(id INT,d1 BIGINT,d2 FLOAT,d3 DOUBLE,PRIMARY KEY (id)); 222 INSERT INTO t VALUES(1,12345678977,4679.45,-46576898.09877); 223 INSERT INTO t VALUES(2,4251382834,-456.785,32913023.3213); 224 INSERT INTO t VALUES(3,-46382749832,0,456215.454); 225 INSERT INTO t VALUES(4,0,8.121,0); 226 INSERT INTO t VALUES(5,-329323809293,0,0); 227 INSERT INTO t VALUES(6,47832745,4672493280324.37644342323242,-1.8976931348623157E+308); 228 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 85 near "1.8976931348623157E+308);"; 229 INSERT INTO t VALUES(7,47832745,4.402823466351E+38,666.666); 230 Data truncation: data out of range: data type float32, value '4.402823466351e+38' 231 INSERT INTO t VALUES(6,47832745,4672493280324.37644342323242,-1.8976931348623157E+308); 232 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 85 near "1.8976931348623157E+308);"; 233 INSERT INTO t VALUES(7,47832745,4.402823466351E+38,666.666); 234 Data truncation: data out of range: data type float32, value '4.402823466351e+38' 235 SELECT left(d1,abs(-5)) from t; 236 left(d1, abs(-5)) 237 12345 238 42513 239 -4638 240 0 241 -3293 242 SELECT left(d2, LENGTH(d1) - 5) from t; 243 left(d2, length(d1) - 5) 244 4679.4 245 -456. 246 0 247 248 0 249 SELECT left(d1, 3),left(d2, 6) from t ORDER by d1; 250 left(d1, 3) left(d2, 6) 251 -32 0 252 -46 0 253 0 8.121 254 425 -456.7 255 123 4679.4 256 SELECT * from t where ABS(d1) > 200000; 257 id d1 d2 d3 258 1 12345678977 4679.45 -4.657689809877E7 259 2 4251382834 -456.785 3.29130233213E7 260 3 -46382749832 0.0 456215.454 261 5 -329323809293 0.0 0.0 262 DROP TABLE IF EXISTS t; 263 DROP TABLE IF EXISTS t1; 264 CREATE TABLE t(d INT,d1 VARCHAR(20), d2 BIGINT,PRIMARY KEY (d)); 265 CREATE TABLE t1( d INT,d1 CHAR(20),d2 DATE,PRIMARY KEY (d)); 266 INSERT INTO t VALUES (1,'lijklnfdsalj',19290988), (2,'xlziblkfdi',1949100132); 267 INSERT INTO t VALUES (3,'ixioklakmaria',69456486), (4,'brzilaiusd',6448781575); 268 INSERT INTO t1 VALUES (1,'usaisagoodnat','1970-01-02'),(2,'chanialfakbjap','1971-11-12'); 269 INSERT INTO t1 VALUES (3,'indiaisashit','1972-09-09'),(4,'xingoporelka','1973-12-07'); 270 SELECT t.d, LEFT(t.d1, abs(-4)) FROM t; 271 d left(t.d1, abs(-4)) 272 1 lijk 273 2 xlzi 274 3 ixio 275 4 brzi 276 SELECT t.d, LEFT(t.d2, FIND_IN_SET('d','a,b,c,d')), LEFT(t1.d1, ABS(-3)+1) FROM t,t1 WHERE t.d = t1.d; 277 d left(t.d2, find_in_set(d, a,b,c,d)) left(t1.d1, abs(-3) + 1) 278 1 1929 usai 279 2 1949 chan 280 3 6945 indi 281 4 6448 xing 282 SELECT t.d, LEFT(t1.d2, NULL) FROM t JOIN t1 ON t.d = t1.d; 283 d left(t1.d2, null) 284 1 null 285 2 null 286 3 null 287 4 null 288 SELECT t.d,left(t1.d2, abs(-1)+1),left(t.d2, cos(0)+3) from t join t1 on t.d=t1.d; 289 d left(t1.d2, abs(-1) + 1) left(t.d2, cos(0) + 3) 290 1 19 1929 291 2 19 1949 292 3 19 6945 293 4 19 6448 294 SELECT t.d,left(t.d2, find_in_set('e','a,b,c,d,e')),left(t1.d1, 20%3)from t right join t1 on t.d=t1.d; 295 d left(t.d2, find_in_set(e, a,b,c,d,e)) left(t1.d1, 20 % 3) 296 1 19290 us 297 2 19491 ch 298 3 69456 in 299 4 64487 xi 300 SELECT t.d,left(t.d1, find_in_set('d','a,b,c,d,e')),left(t1.d2, 20%3)from t right join t1 on t.d=t1.d; 301 d left(t.d1, find_in_set(d, a,b,c,d,e)) left(t1.d2, 20 % 3) 302 1 lijk 19 303 2 xlzi 19 304 3 ixio 19 305 4 brzi 19 306 DROP TABLE IF EXISTS t; 307 DROP TABLE IF EXISTS t1;