github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_string_lpad_rpad.test (about)

     1  #SELECT 
     2  SELECT LPAD('hi',4,'??');
     3  SELECT LPAD('hi',1,'??');
     4  SELECT RPAD('hi',5,'?');
     5  SELECT RPAD('hi',1,'?');
     6  
     7  #SELECT,嵌套
     8  CREATE TABLE t1 (title varchar(255));
     9  INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education');
    10  INSERT INTO t1 VALUES ('House passes the CAREERS bill');
    11  SELECT RPAD("",(55 - LENGTH(title)),".") from t1;
    12  DROP TABLE t1;
    13  
    14  #EXTREME VALUE
    15  select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'), rpad(11, 10 , 22), rpad("ab", 10, 22);
    16  select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12'), lpad(11, 10 , 22);
    17  select rpad(741653838,17,'0'),lpad(741653838,17,'0');
    18  select rpad('abcd',7,'ab'),lpad('abcd',7,'ab');
    19  select rpad('abcd',1,'ab'),lpad('abcd',1,'ab');
    20  select rpad('STRING', 20, CONCAT_WS('p','a','d') );
    21  select lpad('STRING', 20, CONCAT_WS('p','a','d') );
    22  select rpad('hello', -1, '1');
    23  select rpad('hello', -4294967295, '1');
    24  select rpad('hello', 4294967295, '1');
    25  select rpad('hello', -4294967296, '1');
    26  select rpad('hello', 4294967296, '1');
    27  select rpad('hello', -4294967297, '1');
    28  select rpad('hello', 4294967297, '1');
    29  
    30  select rpad('hello', -18446744073709551615, '1');
    31  select rpad('hello', 18446744073709551615, '1');
    32  select rpad('hello', -18446744073709551616, '1');
    33  select rpad('hello', 18446744073709551616, '1');
    34  select rpad('hello', -18446744073709551617, '1');
    35  select rpad('hello', 18446744073709551617, '1');
    36  
    37  select lpad('hello', -1, '1');
    38  select lpad('hello', -4294967295, '1');
    39  select lpad('hello', 4294967295, '1');
    40  select lpad('hello', -4294967296, '1');
    41  select lpad('hello', 4294967296, '1');
    42  select lpad('hello', -4294967297, '1');
    43  select lpad('hello', 4294967297, '1');
    44  
    45  select lpad('hello', -18446744073709551615, '1');
    46  select lpad('hello', 18446744073709551615, '1');
    47  select lpad('hello', -18446744073709551616, '1');
    48  select lpad('hello', 18446744073709551616, '1');
    49  select lpad('hello', -18446744073709551617, '1');
    50  select lpad('hello', 18446744073709551617, '1');
    51  
    52  SELECT RPAD('hi', year(FROM_UNIXTIME(-1)),'?');
    53  SELECT LPAD('hi', year(FROM_UNIXTIME(-1)),'?');
    54  
    55  
    56  #NULL
    57  SELECT LPAD(NULL, 5, 'x') AS result;
    58  SELECT LPAD(NULL, NULL, 'x') AS result;
    59  SELECT LPAD(NULL, NULL, NULL) AS result;
    60  SELECT LPAD('a', NULL, 'x') AS result;
    61  SELECT LPAD('a', NULL, NULL) AS result;
    62  SELECT LPAD('a', 5, NULL) AS result;
    63  SELECT LPAD(NULL, 5, NULL) AS result;
    64  SELECT LPAD('a', 0, 'x') AS result;
    65  SELECT LPAD('a', 0, '') AS result;
    66  SELECT LPAD('', 0, 'x') AS result;
    67  SELECT LPAD('', 0, '') AS result;
    68  SELECT LPAD('a', -1, 'x');
    69  SELECT RPAD(NULL, 5, 'x') AS result;
    70  SELECT RPAD(NULL, NULL, 'x') AS result;
    71  SELECT RPAD(NULL, NULL, NULL) AS result;
    72  SELECT RPAD('a', NULL, 'x') AS result;
    73  SELECT RPAD('a', NULL, NULL) AS result;
    74  SELECT RPAD('a', 5, NULL) AS result;
    75  SELECT RPAD(NULL, 5, NULL) AS result;
    76  SELECT RPAD('a', 0, 'x') AS result;
    77  SELECT RPAD('a', 0, '') AS result;
    78  SELECT RPAD('', 0, 'x') AS result;
    79  SELECT RPAD('', 0, '') AS result;
    80  SELECT RPAD('a', -1, 'x');
    81  
    82  
    83  #IN
    84  SELECT (rpad(1.0,2048,1)) IS NOT FALSE;
    85  
    86  
    87  SELECT ((+0) IN
    88  ((0b111111111111111111111111111111111111111111111111111),(rpad(1.0,2048,1)),
    89  (32767.1)));
    90  
    91  SELECT ((rpad(1.0,2048,1)) = ('4(') ^ (0.1));
    92  
    93  
    94  #中文
    95  SELECT RPAD("你好", 10, "再见");
    96  SELECT LPAD("你好", 10, "再见");
    97  
    98  #WHERE
    99  drop table if exists t1;
   100  create table t1(a INT,  b varchar(255));
   101  insert into t1 values(1, "2012"),(2, "21241"),(3, "20032"),(4, "200");
   102  select * from t1 where rpad(b, 5, "32")="20032";
   103  drop table t1;
   104  
   105  
   106  #ON CONDITION
   107  create table t1(a INT,  b varchar(255));
   108  create table t2(a INT,  b varchar(255));
   109  insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23");
   110  insert into t2 values(1, "2013-04-30"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-10-12");
   111  SELECT t1.a, t2.a FROM t1 JOIN t2 ON (rpad(t1.b, 12, "ab") = rpad(t2.b, 12, "ab"));
   112  drop table t1;
   113  drop table t2;
   114  
   115  #INSERT, DISTINCT
   116  drop table if exists t1;
   117  create table t1(a varchar(255));
   118  insert into t1 select (rpad('a',4,'1'));
   119  insert into t1 select (rpad('a',4,'1'));
   120  insert into t1 select (rpad('a',4,'1'));
   121  insert into t1 select (lpad('a',4,'1'));
   122  select distinct * from t1;
   123  drop table t1;
   124  
   125  #HAVING
   126  drop table if exists t1;
   127  create table t1(a INT, b varchar(255));
   128  insert into t1 values(1, "2012"),(2, "21241"),(3, "20032"),(4, "200");
   129  select b from t1 group by b having rpad(b, 5, "12")="20012";
   130  drop table t1;
   131  
   132  #DATATYPE
   133  SELECT RPAD("123", 4, 123);
   134  
   135  SELECT RPAD(1234, 7, 123);
   136  SELECT RPAD("123", 4, 123.242);
   137  SELECT RPAD("123", 3.422, 123.242);
   138  
   139  SELECT RPAD(0.341, 3.422, 123.242);
   140  
   141  SELECT LPAD("123", 4, 123);
   142  SELECT LPAD(1234, 7, 123);
   143  SELECT LPAD("123", 4, 123.242);
   144  SELECT LPAD("123", 3.422, 123.242);
   145  SELECT LPAD(0.341, 3.422, 123.242);
   146  
   147  select rpad(binary('a'), 4, 'b');
   148  select lpad(binary('a'), 4, 'b');