github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_string_rtrim_ltrim.test (about) 1 #SELECT 2 SELECT RTRIM('barbar '); 3 SELECT LTRIM(' barbar'); 4 -- @separator:table 5 SELECT ' geeksforgeeks' As OriginalString, 6 LTRIM (' geeksforgeeks') AS LeftTrimmedString; 7 -- @separator:table 8 SELECT ' MySQL' AS String, RTRIM ('MySQL') AS Tstring; 9 10 #SELECT 嵌套 11 #select ltrim("a"),rtrim("a"),trim(BOTH "" from "a"),trim(BOTH " " from "a"); 12 select cast(rtrim(' 20.06 ') as decimal(19,2)); 13 select cast(rtrim(ltrim(' 20.06 ')) as decimal(19,2)); 14 select concat_ws(':',ltrim(' left '),':',rtrim(' right '),':'); 15 16 #WHERE 17 CREATE TABLE t (a int NOT NULL); 18 INSERT INTO t values(21123); 19 SELECT 1 FROM t WHERE rtrim(a & NULL); 20 SELECT 1 FROM t WHERE ltrim(a & NULL); 21 DROP TABLE t; 22 23 #特殊字符集 24 25 drop table if exists `Ｔ１`; 26 drop table if exists `Ｔ２`; 27 drop table if exists `Ｔ３`; 28 drop table if exists `Ｔ４`; 29 drop table if exists `Ｔ５`; 30 drop table if exists `Ｔ６`; 31 drop table if exists `Ｔ７`; 32 drop table if exists `Ｔ８`; 33 drop table if exists `Ｔ９`; 34 CREATE TABLE `Ｔ１` (`Ｃ１` char(80)); 35 CREATE TABLE `Ｔ２` (`Ｃ１` char(80)); 36 CREATE TABLE `Ｔ３` (`Ｃ１` char(80)); 37 CREATE TABLE `Ｔ４` (`Ｃ１` char(80)); 38 CREATE TABLE `Ｔ５` (`Ｃ１` char(80)); 39 CREATE TABLE `Ｔ６` (`Ｃ１` char(80)); 40 CREATE TABLE `Ｔ７` (`Ｃ１` char(80)); 41 CREATE TABLE `Ｔ８` (`Ｃ１` char(80)); 42 CREATE TABLE `Ｔ９` (`Ｃ１` char(80)); 43 INSERT INTO `Ｔ１` VALUES ('ｱｲｳｴｵ'); 44 INSERT INTO `Ｔ１` VALUES ('ｱｲｳｴｵ '); 45 INSERT INTO `Ｔ１` VALUES ('ｱｲｳｴｵ '); 46 INSERT INTO `Ｔ１` VALUES ('ｱｲｳｴｵ '); 47 INSERT INTO `Ｔ１` VALUES ('ｱｲｳｴｵ '); 48 INSERT INTO `Ｔ１` VALUES ('ｱｲｳｴｵ '); 49 INSERT INTO `Ｔ１` VALUES ('ｱｲｳｴｵ '); 50 INSERT INTO `Ｔ２` VALUES ('あいうえお'); 51 INSERT INTO `Ｔ２` VALUES ('あいうえお '); 52 INSERT INTO `Ｔ２` VALUES ('あいうえお '); 53 INSERT INTO `Ｔ２` VALUES ('あいうえお '); 54 INSERT INTO `Ｔ２` VALUES ('あいうえお '); 55 INSERT INTO `Ｔ２` VALUES ('あいうえお '); 56 INSERT INTO `Ｔ２` VALUES ('あいうえお '); 57 INSERT INTO `Ｔ３` VALUES ('龔龖龗龞龡'); 58 INSERT INTO `Ｔ３` VALUES ('龔龖龗龞龡 '); 59 INSERT INTO `Ｔ３` VALUES ('龔龖龗龞龡 '); 60 INSERT INTO `Ｔ３` VALUES ('龔龖龗龞龡 '); 61 INSERT INTO `Ｔ３` VALUES ('龔龖龗龞龡 '); 62 INSERT INTO `Ｔ３` VALUES ('龔龖龗龞龡 '); 63 INSERT INTO `Ｔ３` VALUES ('龔龖龗龞龡 '); 64 INSERT INTO `Ｔ４` VALUES ('ｱｲｳｴｵ'); 65 INSERT INTO `Ｔ４` VALUES ('ｱｲｳｴｵ '); 66 INSERT INTO `Ｔ４` VALUES ('ｱｲｳｴｵ '); 67 INSERT INTO `Ｔ４` VALUES ('ｱｲｳｴｵ '); 68 INSERT INTO `Ｔ４` VALUES ('ｱｲｳｴｵ '); 69 INSERT INTO `Ｔ４` VALUES ('ｱｲｳｴｵ '); 70 INSERT INTO `Ｔ４` VALUES ('ｱｲｳｴｵ '); 71 INSERT INTO `Ｔ５` VALUES ('あいうえお'); 72 INSERT INTO `Ｔ５` VALUES ('あいうえお '); 73 INSERT INTO `Ｔ５` VALUES ('あいうえお '); 74 INSERT INTO `Ｔ５` VALUES ('あいうえお '); 75 INSERT INTO `Ｔ５` VALUES ('あいうえお '); 76 INSERT INTO `Ｔ５` VALUES ('あいうえお '); 77 INSERT INTO `Ｔ５` VALUES ('あいうえお '); 78 INSERT INTO `Ｔ６` VALUES ('龔龖龗龞龡'); 79 INSERT INTO `Ｔ６` VALUES ('龔龖龗龞龡 '); 80 INSERT INTO `Ｔ６` VALUES ('龔龖龗龞龡 '); 81 INSERT INTO `Ｔ６` VALUES ('龔龖龗龞龡 '); 82 INSERT INTO `Ｔ６` VALUES ('龔龖龗龞龡 '); 83 INSERT INTO `Ｔ６` VALUES ('龔龖龗龞龡 '); 84 INSERT INTO `Ｔ６` VALUES ('龔龖龗龞龡 '); 85 INSERT INTO `Ｔ７` VALUES ('ｱｲｳｴｵ'); 86 INSERT INTO `Ｔ７` VALUES ('ｱｲｳｴｵ '); 87 INSERT INTO `Ｔ７` VALUES ('ｱｲｳｴｵ '); 88 INSERT INTO `Ｔ７` VALUES ('ｱｲｳｴｵ '); 89 INSERT INTO `Ｔ７` VALUES ('ｱｲｳｴｵ '); 90 INSERT INTO `Ｔ７` VALUES ('ｱｲｳｴｵ '); 91 INSERT INTO `Ｔ７` VALUES ('ｱｲｳｴｵ '); 92 INSERT INTO `Ｔ８` VALUES ('あいうえお'); 93 INSERT INTO `Ｔ８` VALUES ('あいうえお '); 94 INSERT INTO `Ｔ８` VALUES ('あいうえお '); 95 INSERT INTO `Ｔ８` VALUES ('あいうえお '); 96 INSERT INTO `Ｔ８` VALUES ('あいうえお '); 97 INSERT INTO `Ｔ８` VALUES ('あいうえお '); 98 INSERT INTO `Ｔ８` VALUES ('あいうえお '); 99 INSERT INTO `Ｔ９` VALUES ('龔龖龗龞龡'); 100 INSERT INTO `Ｔ９` VALUES ('龔龖龗龞龡 '); 101 INSERT INTO `Ｔ９` VALUES ('龔龖龗龞龡 '); 102 INSERT INTO `Ｔ９` VALUES ('龔龖龗龞龡 '); 103 INSERT INTO `Ｔ９` VALUES ('龔龖龗龞龡 '); 104 INSERT INTO `Ｔ９` VALUES ('龔龖龗龞龡 '); 105 INSERT INTO `Ｔ９` VALUES ('龔龖龗龞龡 '); 106 SELECT RTRIM(`Ｃ１`) from `Ｔ１`; 107 SELECT RTRIM(`Ｃ１`) from `Ｔ２`; 108 SELECT RTRIM(`Ｃ１`) from `Ｔ３`; 109 SELECT RTRIM(`Ｃ１`) from `Ｔ４`; 110 SELECT RTRIM(`Ｃ１`) from `Ｔ５`; 111 SELECT RTRIM(`Ｃ１`) from `Ｔ６`; 112 SELECT RTRIM(`Ｃ１`) from `Ｔ７`; 113 SELECT RTRIM(`Ｃ１`) from `Ｔ８`; 114 SELECT RTRIM(`Ｃ１`) from `Ｔ９`; 115 DROP TABLE `Ｔ１`; 116 DROP TABLE `Ｔ２`; 117 DROP TABLE `Ｔ３`; 118 DROP TABLE `Ｔ４`; 119 DROP TABLE `Ｔ５`; 120 DROP TABLE `Ｔ６`; 121 DROP TABLE `Ｔ７`; 122 DROP TABLE `Ｔ８`; 123 DROP TABLE `Ｔ９`; 124 drop table if exists `Ｔ１`; 125 drop table if exists `Ｔ２`; 126 drop table if exists `Ｔ３`; 127 drop table if exists `Ｔ４`; 128 drop table if exists `Ｔ５`; 129 drop table if exists `Ｔ６`; 130 drop table if exists `Ｔ７`; 131 drop table if exists `Ｔ８`; 132 drop table if exists `Ｔ９`; 133 134 135 #NULL 136 SELECT RTRIM(NULL); 137 SELECT LTRIM(NULL); 138 139 #中文 140 SELECT RTRIM("你好 "); 141 SELECT LTRIM(" 你好"); 142 143 #INSERT, distinct 144 drop table if exists t1; 145 create table t1(a varchar(255)); 146 insert into t1 select (RTRIM("2017-06-15 ")); 147 insert into t1 select (LTRIM(" 2019-06-25")); 148 insert into t1 select (RTRIM("2017-06-15 ")); 149 insert into t1 select ("2017-06-15 "); 150 select distinct RTRIM(ltrim(a)),LTRIM(rtrim(a)) from t1; 151 drop table t1; 152 153 154 #HAVING & 算术运算 155 drop table if exists t1; 156 create table t1(a INT, b datetime); 157 insert into t1 values(1, "2017-06-15 "),(1, "2019-06-25 "),(2, " 2019-06-25 "),(3, " 2019-06-25 "); 158 select b from t1 group by b having rtrim(ltrim(b))>"2018-01-01"; 159 drop table t1; 160 161 #ON CONDITION 162 create table t1(a INT, b date); 163 create table t2(a INT, b date); 164 insert into t1 values(1, " 2012-10-12 "),(2, " 2004-04-24. "),(3, " 2008-12-04. "),(4, " 2012-03-23. "); 165 insert into t2 values(1, " 2013-04-30 "),(2, " 1994-10-04 "),(3, " 2018-06-04 "),(4, " 2012-10-12 "); 166 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (rtrim(ltrim(t1.b)) = ltrim(rtrim(t2.b))); 167 drop table t1; 168 drop table t2; 169 170 #EXTREME VALUE 171 SELECT RTRIM(space(100000000)+"123"); 172 SELECT LTRIM("123"+space(100000000)); 173 SELECT LTRIM(" 1241241^&@%#^*^!@#&*(!&"); 174 SELECT RTRIM("1241241^&@%#^*^!@#&*(!& "); 175 176 #DATETYPE 177 SELECT RTRIM(" 123 "); 178 SELECT RTRIM(123124); 179 SELECT RTRIM(123.41231); 180 SELECT LTRIM(" 123"); 181 SELECT LTRIM(123124); 182 SELECT LTRIM(123.41231); 183