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

     1  drop table if exists t1;
     2  create table t1 ( id int, c char(10),vc varchar(20));
     3  insert into t1 values (1,'Daffy','Aducklife');
     4  insert into t1 values (1,'Daffy','Aducklife');
     5  insert into t1 values (2,'Bugs','Arabbitlife');
     6  insert into t1 values (3,'Cowboy','Lifeontherange');
     7  insert into t1 values (4,'Anonymous','Wannabuythisbook?');
     8  insert into t1 values (5,'BestSeller','OneHeckuvabook');
     9  insert into t1 values (5,'BestSeller','OneHeckuvabook');
    10  insert into t1 values (6,'EveryoneBu','Thisverybook');
    11  insert into t1 values (7,'SanFran','Itisasanfranlifetyle');
    12  insert into t1 values (8,'BerkAuthor','Cool.Berkly.the.book');
    13  insert into t1 values (9,null,null);
    14  insert into t1 values (10,'','');
    15  select SUBSTRING(c,1),SUBSTR(vc,1) from t1;
    16  SUBSTRING(c,1)	SUBSTR(vc,1)
    17  Daffy	Aducklife
    18  Daffy	Aducklife
    19  Bugs	Arabbitlife
    20  Cowboy	Lifeontherange
    21  Anonymous	Wannabuythisbook?
    22  BestSeller	OneHeckuvabook
    23  BestSeller	OneHeckuvabook
    24  EveryoneBu	Thisverybook
    25  SanFran	Itisasanfranlifetyle
    26  BerkAuthor	Cool.Berkly.the.book
    27  null	null
    28  
    29  select SUBSTRING(c,2),SUBSTR(vc,2) from t1;
    30  SUBSTRING(c,2)	SUBSTR(vc,2)
    31  affy	ducklife
    32  affy	ducklife
    33  ugs	rabbitlife
    34  owboy	ifeontherange
    35  nonymous	annabuythisbook?
    36  estSeller	neHeckuvabook
    37  estSeller	neHeckuvabook
    38  veryoneBu	hisverybook
    39  anFran	tisasanfranlifetyle
    40  erkAuthor	ool.Berkly.the.book
    41  null	null
    42  
    43  select SUBSTRING(c,5),SUBSTR(vc,9) from t1 where id = 1 ;
    44  SUBSTRING(c,5)	SUBSTR(vc,9)
    45  y	e
    46  y	e
    47  select substring(c,11),substr(vc,13) from t1 where id = 6 ;
    48  substring(c,11)	substr(vc,13)
    49  
    50  select SUBSTRING(c,-1),SUBSTR(vc,-1) from t1;
    51  SUBSTRING(c,-1)	SUBSTR(vc,-1)
    52  y	e
    53  y	e
    54  s	e
    55  y	e
    56  s	?
    57  r	k
    58  r	k
    59  u	k
    60  n	e
    61  r	k
    62  null	null
    63  
    64  select SUBSTRING(c,-2),SUBSTR(vc,-2) from t1;
    65  SUBSTRING(c,-2)	SUBSTR(vc,-2)
    66  fy	fe
    67  fy	fe
    68  gs	fe
    69  oy	ge
    70  us	k?
    71  er	ok
    72  er	ok
    73  Bu	ok
    74  an	le
    75  or	ok
    76  null	null
    77  
    78  select SUBSTRING(c,-5),SUBSTR(vc,-9) from t1 where id = 1 ;
    79  SUBSTRING(c,-5)	SUBSTR(vc,-9)
    80  Daffy	Aducklife
    81  Daffy	Aducklife
    82  select substring(c,-11),substr(vc,-13) from t1 where id = 6 ;
    83  substring(c,-11)	substr(vc,-13)
    84  
    85  select SUBSTRING(c,1,1),SUBSTR(vc,1,1) from t1 where id = 1 ;
    86  SUBSTRING(c,1,1)	SUBSTR(vc,1,1)
    87  D	A
    88  D	A
    89  select SUBSTRING(c,2,2),SUBSTR(vc,3,3) from t1 where id = 1 ;
    90  SUBSTRING(c,2,2)	SUBSTR(vc,3,3)
    91  af	uck
    92  af	uck
    93  select SUBSTRING(c,2,5),SUBSTR(vc,3,9) from t1 where id = 1 ;
    94  SUBSTRING(c,2,5)	SUBSTR(vc,3,9)
    95  affy	ucklife
    96  affy	ucklife
    97  select substring(c,2,9),substr(vc,3,17) from t1 where id = 4 ;
    98  substring(c,2,9)	substr(vc,3,17)
    99  nonymous	nnabuythisbook?
   100  select substring(c,2,10),substr(vc,3,18) from t1 where id = 4 ;
   101  substring(c,2,10)	substr(vc,3,18)
   102  nonymous	nnabuythisbook?
   103  select substring(c,5,4),substr(vc,5,4) from t1 where id = 4 ;
   104  substring(c,5,4)	substr(vc,5,4)
   105  ymou	abuy
   106  select substring(c,10,10),substr(vc,18,18) from t1 where id = 4 ;
   107  substring(c,10,10)	substr(vc,18,18)
   108  
   109  select substring(c,2,-1),substr(vc,3,-1) from t1 where id = 4 ;
   110  substring(c,2,-1)	substr(vc,3,-1)
   111  
   112  select SUBSTRING(c,-1,1),SUBSTR(vc,1,1) from t1 where id = 1 ;
   113  SUBSTRING(c,-1,1)	SUBSTR(vc,1,1)
   114  y	A
   115  y	A
   116  select SUBSTRING(c,-2,2),SUBSTR(vc,3,3) from t1 where id = 1 ;
   117  SUBSTRING(c,-2,2)	SUBSTR(vc,3,3)
   118  fy	uck
   119  fy	uck
   120  select SUBSTRING(c,-2,5),SUBSTR(vc,3,9) from t1 where id = 1 ;
   121  SUBSTRING(c,-2,5)	SUBSTR(vc,3,9)
   122  fy	ucklife
   123  fy	ucklife
   124  select substring(c,-2,9),substr(vc,3,17) from t1 where id = 4 ;
   125  substring(c,-2,9)	substr(vc,3,17)
   126  us	nnabuythisbook?
   127  select substring(c,-2,10),substr(vc,3,18) from t1 where id = 4 ;
   128  substring(c,-2,10)	substr(vc,3,18)
   129  us	nnabuythisbook?
   130  select substring(c,1,-1),substr(vc,3,-2) from t1 where id = 4 ;
   131  substring(c,1,-1)	substr(vc,3,-2)
   132  
   133  select substring(c,1,a),substr(vc,3,1) from t1 where id = 4 ;
   134  invalid input: column a does not exist
   135  select substring(c,1,2),substr(vc,a,1) from t1 where id = 4 ;
   136  invalid input: column a does not exist
   137  select max(substr(c,2)) from t1;
   138  max(substr(c,2))
   139  veryoneBu
   140  select min(substr(c,2)) from t1;
   141  min(substr(c,2))
   142  
   143  select * from t1 where substr(c,2) = 'affy';
   144  id	c	vc
   145  1	Daffy	Aducklife
   146  1	Daffy	Aducklife
   147  select * from t1 where substr(c,2) <> 'affy';
   148  id	c	vc
   149  2	Bugs	Arabbitlife
   150  3	Cowboy	Lifeontherange
   151  4	Anonymous	Wannabuythisbook?
   152  5	BestSeller	OneHeckuvabook
   153  5	BestSeller	OneHeckuvabook
   154  6	EveryoneBu	Thisverybook
   155  7	SanFran	Itisasanfranlifetyle
   156  8	BerkAuthor	Cool.Berkly.the.book
   157  10
   158  select * from t1 where substr(c,2) > 'affy';
   159  id	c	vc
   160  2	Bugs	Arabbitlife
   161  3	Cowboy	Lifeontherange
   162  4	Anonymous	Wannabuythisbook?
   163  5	BestSeller	OneHeckuvabook
   164  5	BestSeller	OneHeckuvabook
   165  6	EveryoneBu	Thisverybook
   166  7	SanFran	Itisasanfranlifetyle
   167  8	BerkAuthor	Cool.Berkly.the.book
   168  select * from t1 where substr(c,2) > substring('fdasfsad',2);
   169  id	c	vc
   170  2	Bugs	Arabbitlife
   171  3	Cowboy	Lifeontherange
   172  4	Anonymous	Wannabuythisbook?
   173  5	BestSeller	OneHeckuvabook
   174  5	BestSeller	OneHeckuvabook
   175  6	EveryoneBu	Thisverybook
   176  8	BerkAuthor	Cool.Berkly.the.book
   177  select distinct(substr(c,2)) from t1 order by 1;
   178  (substr(c,2))
   179  null
   180  
   181  affy
   182  anFran
   183  erkAuthor
   184  estSeller
   185  nonymous
   186  owboy
   187  ugs
   188  veryoneBu
   189  select distinct(substr(vc,3)) from t1 order by 1;
   190  (substr(vc,3))
   191  null
   192  
   193  abbitlife
   194  eHeckuvabook
   195  feontherange
   196  isasanfranlifetyle
   197  isverybook
   198  nnabuythisbook?
   199  ol.Berkly.the.book
   200  ucklife
   201  select endswith(c,'a'),endswith(vc,'a') from t1;
   202  endswith(c, a)	endswith(vc, a)
   203  false	false
   204  false	false
   205  false	false
   206  false	false
   207  false	false
   208  false	false
   209  false	false
   210  false	false
   211  false	false
   212  false	false
   213  null	null
   214  false	false
   215  select endswith(c,'y'),endswith(vc,'e') from t1;
   216  endswith(c, y)	endswith(vc, e)
   217  true	true
   218  true	true
   219  false	true
   220  true	true
   221  false	false
   222  false	false
   223  false	false
   224  false	false
   225  false	true
   226  false	false
   227  null	null
   228  false	false
   229  select * from t1 where endswith(c,'y');
   230  id	c	vc
   231  1	Daffy	Aducklife
   232  1	Daffy	Aducklife
   233  3	Cowboy	Lifeontherange
   234  select * from t1 where endswith(c,'y') and endswith(vc,'ge');
   235  id	c	vc
   236  3	Cowboy	Lifeontherange
   237  select startswith(c,'B'),startswith(vc,'A') from t1;
   238  startswith(c, B)	startswith(vc, A)
   239  false	true
   240  false	true
   241  true	true
   242  false	false
   243  false	false
   244  true	false
   245  true	false
   246  false	false
   247  false	false
   248  true	false
   249  null	null
   250  false	false
   251  select startswith(c,'y'),startswith(vc,'e') from t1;
   252  startswith(c, y)	startswith(vc, e)
   253  false	false
   254  false	false
   255  false	false
   256  false	false
   257  false	false
   258  false	false
   259  false	false
   260  false	false
   261  false	false
   262  false	false
   263  null	null
   264  false	false
   265  select * from t1 where startswith(c,'B');
   266  id	c	vc
   267  2	Bugs	Arabbitlife
   268  5	BestSeller	OneHeckuvabook
   269  5	BestSeller	OneHeckuvabook
   270  8	BerkAuthor	Cool.Berkly.the.book
   271  select * from t1 where startswith(c,'B') and startswith(vc,'A');
   272  id	c	vc
   273  2	Bugs	Arabbitlife
   274  select lpad(c,0,'*') from t1;
   275  lpad(c,0,'*')
   276  
   277  
   278  
   279  
   280  
   281  
   282  
   283  
   284  
   285  
   286  null
   287  
   288  select lpad(c,1,'*') from t1;
   289  lpad(c,1,'*')
   290  D
   291  D
   292  B
   293  C
   294  A
   295  B
   296  B
   297  E
   298  S
   299  B
   300  null
   301  *
   302  select lpad(c,5,'*') from t1;
   303  lpad(c,5,'*')
   304  Daffy
   305  Daffy
   306  *Bugs
   307  Cowbo
   308  Anony
   309  BestS
   310  BestS
   311  Every
   312  SanFr
   313  BerkA
   314  null
   315  *****
   316  select lpad(c,10,'*') from t1;
   317  lpad(c,10,'*')
   318  *****Daffy
   319  *****Daffy
   320  ******Bugs
   321  ****Cowboy
   322  *Anonymous
   323  BestSeller
   324  BestSeller
   325  EveryoneBu
   326  ***SanFran
   327  BerkAuthor
   328  null
   329  **********
   330  select rpad(c,'1','*') from t1;
   331  rpad(c,'1','*')
   332  D
   333  D
   334  B
   335  C
   336  A
   337  B
   338  B
   339  E
   340  S
   341  B
   342  null
   343  *
   344  select rpad(c,0,'*') from t1;
   345  rpad(c,0,'*')
   346  
   347  
   348  
   349  
   350  
   351  
   352  
   353  
   354  
   355  
   356  null
   357  
   358  select rpad(c,1,'*') from t1;
   359  rpad(c,1,'*')
   360  D
   361  D
   362  B
   363  C
   364  A
   365  B
   366  B
   367  E
   368  S
   369  B
   370  null
   371  *
   372  select rpad(c,5,'*') from t1;
   373  rpad(c,5,'*')
   374  Daffy
   375  Daffy
   376  Bugs*
   377  Cowbo
   378  Anony
   379  BestS
   380  BestS
   381  Every
   382  SanFr
   383  BerkA
   384  null
   385  *****
   386  select rpad(c,10,'*') from t1;
   387  rpad(c,10,'*')
   388  Daffy*****
   389  Daffy*****
   390  Bugs******
   391  Cowboy****
   392  Anonymous*
   393  BestSeller
   394  BestSeller
   395  EveryoneBu
   396  SanFran***
   397  BerkAuthor
   398  null
   399  **********
   400  select rpad(c,'1','*') from t1;
   401  rpad(c,'1','*')
   402  D
   403  D
   404  B
   405  C
   406  A
   407  B
   408  B
   409  E
   410  S
   411  B
   412  null
   413  *
   414  drop table if exists t1;
   415  create table t1 ( id int, c char(20),vc varchar(50));
   416  insert into t1 values (1,'Daffy  ','  Aducklife');
   417  insert into t1 values (1,'  Daffy  ','Aducklife ');
   418  insert into t1 values (2,' Bugs',' Arabbitlife ');
   419  insert into t1 values (3,'    Cowboy',' Lifeontherange');
   420  insert into t1 values (4,' Anonymous  ',' Wannabuythisbook?  ');
   421  insert into t1 values (5,'  BestSeller',' OneHeckuvabook ');
   422  insert into t1 values (5,'  BestSeller','OneHeckuvabook ');
   423  insert into t1 values (6,' EveryoneBu',' Thisverybook ');
   424  insert into t1 values (7,' SanFran',' Itisasanfranlifetyle ');
   425  insert into t1 values (8,' BerkAuthor','  Cool.Berkly.the.book ');
   426  insert into t1 values (9,null,null);
   427  insert into t1 values (10,'','');
   428  select ltrim(c),ltrim(vc) from t1;
   429  ltrim(c)	ltrim(vc)
   430  Daffy  	Aducklife
   431  Daffy  	Aducklife 
   432  Bugs	Arabbitlife 
   433  Cowboy	Lifeontherange
   434  Anonymous  	Wannabuythisbook?  
   435  BestSeller	OneHeckuvabook 
   436  BestSeller	OneHeckuvabook 
   437  EveryoneBu	Thisverybook 
   438  SanFran	Itisasanfranlifetyle 
   439  BerkAuthor	Cool.Berkly.the.book 
   440  null	null
   441  
   442  select rtrim(c),rtrim(vc) from t1;
   443  rtrim(c)	rtrim(vc)
   444  Daffy	  Aducklife
   445    Daffy	Aducklife
   446   Bugs	 Arabbitlife
   447      Cowboy	 Lifeontherange
   448   Anonymous	 Wannabuythisbook?
   449    BestSeller	 OneHeckuvabook
   450    BestSeller	OneHeckuvabook
   451   EveryoneBu	 Thisverybook
   452   SanFran	 Itisasanfranlifetyle
   453   BerkAuthor	  Cool.Berkly.the.book
   454  null	null
   455  
   456  select ltrim(rtrim(c)),rtrim(ltrim(vc)) from t1;
   457  ltrim(rtrim(c))	rtrim(ltrim(vc))
   458  Daffy	Aducklife
   459  Daffy	Aducklife
   460  Bugs	Arabbitlife
   461  Cowboy	Lifeontherange
   462  Anonymous	Wannabuythisbook?
   463  BestSeller	OneHeckuvabook
   464  BestSeller	OneHeckuvabook
   465  EveryoneBu	Thisverybook
   466  SanFran	Itisasanfranlifetyle
   467  BerkAuthor	Cool.Berkly.the.book
   468  null	null
   469  
   470  select * from t1 where ltrim(c) = 'BestSeller';
   471  id	c	vc
   472  5	  BestSeller	 OneHeckuvabook 
   473  5	  BestSeller	OneHeckuvabook 
   474  select * from t1 where ltrim(c) = 'BestSeller' and rtrim(vc) = 'OneHeckuvabook';
   475  id	c	vc
   476  5	  BestSeller	OneHeckuvabook 
   477  drop table if exists t1;
   478  create table t1 ( d int);
   479  insert into t1 values(0),(-1),(2),(10);
   480  select space(d) from t1 where d <> -1;
   481  space(d)
   482  
   483    
   484            
   485  select space(d) from t1;
   486  space(d)
   487  
   488  
   489    
   490            
   491  drop table t1;