>>
>>create table t012t1(a int, b char(10), c varchar(30));

--- SQL operation complete.
>>?ifMX
>>create table t012ut1(a int, b nchar(10), c nchar varying(30));

--- SQL operation complete.
>>?ifMX
>>
>>create table t012t3(
+>    a smallint not null, b smallint, 
+>    c smallint unsigned not null, d smallint unsigned,
+>    e int not null, f int,
+>    g int unsigned not null, h int unsigned,
+>    i largeint not null,
+>    j decimal(3,0) , k decimal(2,0) unsigned not null,
+>    l decimal(7,0) not null, m decimal(9,0) unsigned,
+>    n decimal(17,0), o decimal(9,0) unsigned
+>    ) no partition;

--- SQL operation complete.
>>
>>?section dml
>>-- INSERT queries
>>insert into t012t1 values(10, 'abcdef', 'ghij');

--- 1 row(s) inserted.
>>insert into t012t1 values(20, 'ABCDEF', 'GHIJ');

--- 1 row(s) inserted.
>>insert into t012t1 values(30, '   abc   ', '   def   ');

--- 1 row(s) inserted.
>>insert into t012t1 values(40, 'test', 'trim on non-space');

--- 1 row(s) inserted.
>>
>>?ifMX
>>insert into t012ut1 values(10, N'abcdef', N'ghij');

--- 1 row(s) inserted.
>>insert into t012ut1 values(20, N'ABCDEF', N'GHIJ');

--- 1 row(s) inserted.
>>insert into t012ut1 values(30, N'   abc   ', N'   def   ');

--- 1 row(s) inserted.
>>insert into t012ut1 values(40, N'test', N'trim on non-space');

--- 1 row(s) inserted.
>>?ifMX
>>
>>insert into t012t3 values
+>  (0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
+>  (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1),
+>  (3,3,3,3,3,3,3,3,3,3,3,3,3,3,3),
+>  (4,4,4,4,4,4,4,4,4,4,4,4,4,4,4),
+>  (-1,-1,1,1,-1,-1,1,1,-1,-1,1,-1,1,-1,1),
+>  (-3,-3,3,3,-3,-3,3,3,-3,-3,3,-3,3,-3,3),
+>  (-4,-4,4,4,-4,-4,4,4,-4,-4,4,-4,4,-4,4),
+>  (0,1,0,1,0,1,0,1,0,0,1,0,1,0,1);

--- 8 row(s) inserted.
>>
>>insert into t012t3 values 
+>(0, null, 0, null, 0, null, 0, null, 0, 
+> null, 0, 0,  null, null, null);

--- 1 row(s) inserted.
>>
>>-- SELECT queries
>>select * from t012t1;

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          
         30     abc         def                        
         40  test        trim on non-space             

--- 4 row(s) selected.
>>
>>?ifMX
>>select * from t012ut1;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        
         30     abc                   def                                                      
         40  test                  trim on non-space                                           

--- 4 row(s) selected.
>>?ifMX
>>
>>-- testing LOWER function
>>select lower(b),lower(c) from t012t1;

(EXPR)      (EXPR)                        
----------  ------------------------------

abcdef      ghij                          
abcdef      ghij                          
   abc         def                        
test        trim on non-space             

--- 4 row(s) selected.
>>select lower(lower(b)),lower(lower(c)) from t012t1;

(EXPR)      (EXPR)                        
----------  ------------------------------

abcdef      ghij                          
abcdef      ghij                          
   abc         def                        
test        trim on non-space             

--- 4 row(s) selected.
>>select * from t012t1 where lower(c) = 'ghij';

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          

--- 2 row(s) selected.
>>
>>?ifMX
>>select lower(b),lower(c) from t012ut1;

(EXPR)                (EXPR)
--------------------  ------------------------------------------------------------

abcdef                ghij                                                        
abcdef                ghij                                                        
   abc                   def                                                      
test                  trim on non-space                                           

--- 4 row(s) selected.
>>select lower(lower(b)),lower(lower(c)) from t012ut1;

(EXPR)                (EXPR)
--------------------  ------------------------------------------------------------

abcdef                ghij                                                        
abcdef                ghij                                                        
   abc                   def                                                      
test                  trim on non-space                                           

--- 4 row(s) selected.
>>select * from t012ut1 where lower(c) = N'ghij';

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        

--- 2 row(s) selected.
>>?ifMX
>>
>>-- testing UPPER function
>>select upper(b),upper(c) from t012t1;

(EXPR)      (EXPR)                        
----------  ------------------------------

ABCDEF      GHIJ                          
ABCDEF      GHIJ                          
   ABC         DEF                        
TEST        TRIM ON NON-SPACE             

--- 4 row(s) selected.
>>select upper(upper(b)), upper(upper(c)) from t012t1;

(EXPR)      (EXPR)                        
----------  ------------------------------

ABCDEF      GHIJ                          
ABCDEF      GHIJ                          
   ABC         DEF                        
TEST        TRIM ON NON-SPACE             

--- 4 row(s) selected.
>>select * from t012t1 where upper(c) = 'GHIJ';

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          

--- 2 row(s) selected.
>>
>>?ifMX
>>select upper(b),upper(c) from t012ut1;

(EXPR)                                                        (EXPR)
------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ABCDEF                                                        GHIJ                                                                                                                                                                                
ABCDEF                                                        GHIJ                                                                                                                                                                                
   ABC                                                           DEF                                                                                                                                                                              
TEST                                                          TRIM ON NON-SPACE                                                                                                                                                                   

--- 4 row(s) selected.
>>select upper(upper(b)), upper(upper(c)) from t012ut1;

(EXPR)                                                        (EXPR)
------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ABCDEF                                                        GHIJ                                                                                                                                                                                
ABCDEF                                                        GHIJ                                                                                                                                                                                
   ABC                                                           DEF                                                                                                                                                                              
TEST                                                          TRIM ON NON-SPACE                                                                                                                                                                   

--- 4 row(s) selected.
>>select * from t012ut1 where upper(c) = N'GHIJ';

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        

--- 2 row(s) selected.
>>?ifMX
>>
>>-- testing UPSHIFT function (same as UPPER)
>>select upshift(b),upshift(c) from t012t1;

(EXPR)      (EXPR)                        
----------  ------------------------------

ABCDEF      GHIJ                          
ABCDEF      GHIJ                          
   ABC         DEF                        
TEST        TRIM ON NON-SPACE             

--- 4 row(s) selected.
>>select upshift(upshift(b)), upshift(upshift(c)) from t012t1;

(EXPR)      (EXPR)                        
----------  ------------------------------

ABCDEF      GHIJ                          
ABCDEF      GHIJ                          
   ABC         DEF                        
TEST        TRIM ON NON-SPACE             

--- 4 row(s) selected.
>>select * from t012t1 where upshift(c) = 'GHIJ';

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          

--- 2 row(s) selected.
>>
>>?ifMX
>>select upshift(b),upshift(c) from t012ut1;

(EXPR)                                                        (EXPR)
------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ABCDEF                                                        GHIJ                                                                                                                                                                                
ABCDEF                                                        GHIJ                                                                                                                                                                                
   ABC                                                           DEF                                                                                                                                                                              
TEST                                                          TRIM ON NON-SPACE                                                                                                                                                                   

--- 4 row(s) selected.
>>select upshift(upshift(b)), upshift(upshift(c)) from t012ut1;

(EXPR)                                                        (EXPR)
------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ABCDEF                                                        GHIJ                                                                                                                                                                                
ABCDEF                                                        GHIJ                                                                                                                                                                                
   ABC                                                           DEF                                                                                                                                                                              
TEST                                                          TRIM ON NON-SPACE                                                                                                                                                                   

--- 4 row(s) selected.
>>select * from t012ut1 where upshift(c) = N'GHIJ';

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        

--- 2 row(s) selected.
>>?ifMX
>>
>>-- testing LOWER and UPPER together
>>select lower(upper(b)),lower(upper(c)) from t012t1;

(EXPR)      (EXPR)                        
----------  ------------------------------

abcdef      ghij                          
abcdef      ghij                          
   abc         def                        
test        trim on non-space             

--- 4 row(s) selected.
>>select upper(lower(b)),upper(lower(c)) from t012t1;

(EXPR)      (EXPR)                        
----------  ------------------------------

ABCDEF      GHIJ                          
ABCDEF      GHIJ                          
   ABC         DEF                        
TEST        TRIM ON NON-SPACE             

--- 4 row(s) selected.
>>select * from t012t1 where lower(upper(c)) = 'ghij';

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          

--- 2 row(s) selected.
>>select * from t012t1 where upper(lower(c)) = 'GHIJ';

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          

--- 2 row(s) selected.
>>
>>?ifMX
>>select lower(upper(b)),lower(upper(c)) from t012ut1;

(EXPR)                                                        (EXPR)
------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

abcdef                                                        ghij                                                                                                                                                                                
abcdef                                                        ghij                                                                                                                                                                                
   abc                                                           def                                                                                                                                                                              
test                                                          trim on non-space                                                                                                                                                                   

--- 4 row(s) selected.
>>select upper(lower(b)),upper(lower(c)) from t012ut1;

(EXPR)                                                        (EXPR)
------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ABCDEF                                                        GHIJ                                                                                                                                                                                
ABCDEF                                                        GHIJ                                                                                                                                                                                
   ABC                                                           DEF                                                                                                                                                                              
TEST                                                          TRIM ON NON-SPACE                                                                                                                                                                   

--- 4 row(s) selected.
>>select * from t012ut1 where lower(upper(c)) = N'ghij';

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        

--- 2 row(s) selected.
>>select * from t012ut1 where upper(lower(c)) = N'GHIJ';

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        

--- 2 row(s) selected.
>>?ifMX
>>
>>-- testing CHAR_LENGTH function
>>select b,char_length(b),c,char_length(c) from t012t1;

B           (EXPR)      C                               (EXPR)    
----------  ----------  ------------------------------  ----------

abcdef              10  ghij                                     4
ABCDEF              10  GHIJ                                     4
   abc              10     def                                   9
test                10  trim on non-space                       17

--- 4 row(s) selected.
>>select max(char_length(b)), max(char_length(c)) from t012t1;

(EXPR)      (EXPR)    
----------  ----------

        10          17

--- 1 row(s) selected.
>>select min(char_length(b)), min(char_length(c)) from t012t1;

(EXPR)      (EXPR)    
----------  ----------

        10           4

--- 1 row(s) selected.
>>select sum(char_length(b)), sum(char_length(c)) from t012t1;

(EXPR)                (EXPR)              
--------------------  --------------------

                  40                    34

--- 1 row(s) selected.
>>select avg(char_length(b)), avg(char_length(c)) from t012t1;

(EXPR)                (EXPR)              
--------------------  --------------------

                  10                     8

--- 1 row(s) selected.
>>select distinct char_length(b), char_length(c) from t012t1;

(EXPR)      (EXPR)    
----------  ----------

        10           4
        10           9
        10          17

--- 3 row(s) selected.
>>select b,char_length(b),'',char_length('') from t012t1;

B           (EXPR)      (EXPR)  (EXPR)    
----------  ----------  ------  ----------

abcdef              10                   0
ABCDEF              10                   0
   abc              10                   0
test                10                   0

--- 4 row(s) selected.
>>select * from t012t1 where char_length(c) = 4;

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          

--- 2 row(s) selected.
>>select * from t012t1 where char_length(b) + 5 = 15;

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          
         30     abc         def                        
         40  test        trim on non-space             

--- 4 row(s) selected.
>>select * from t012t1 where char_length(c) <> 4;

A            B           C                             
-----------  ----------  ------------------------------

         30     abc         def                        
         40  test        trim on non-space             

--- 2 row(s) selected.
>>select * from t012t1 where char_length(c) > 4;

A            B           C                             
-----------  ----------  ------------------------------

         30     abc         def                        
         40  test        trim on non-space             

--- 2 row(s) selected.
>>select * from t012t1 where char_length(c) >= 4;

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          
         30     abc         def                        
         40  test        trim on non-space             

--- 4 row(s) selected.
>>select * from t012t1 where char_length(c) < 5;

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          

--- 2 row(s) selected.
>>select * from t012t1 where char_length(c) <= 4;

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          

--- 2 row(s) selected.
>>
>>?ifMX
>>select b,char_length(b),c,char_length(c) from t012ut1;

B                     (EXPR)      C                                                             (EXPR)
--------------------  ----------  ------------------------------------------------------------  ----------

abcdef                        10  ghij                                                                   4
ABCDEF                        10  GHIJ                                                                   4
   abc                        10     def                                                                 9
test                          10  trim on non-space                                                     17

--- 4 row(s) selected.
>>select max(char_length(b)), max(char_length(c)) from t012ut1;

(EXPR)      (EXPR)    
----------  ----------

        10          17

--- 1 row(s) selected.
>>select min(char_length(b)), min(char_length(c)) from t012ut1;

(EXPR)      (EXPR)    
----------  ----------

        10           4

--- 1 row(s) selected.
>>select sum(char_length(b)), sum(char_length(c)) from t012ut1;

(EXPR)                (EXPR)              
--------------------  --------------------

                  40                    34

--- 1 row(s) selected.
>>select avg(char_length(b)), avg(char_length(c)) from t012ut1;

(EXPR)                (EXPR)              
--------------------  --------------------

                  10                     8

--- 1 row(s) selected.
>>select distinct char_length(b), char_length(c) from t012ut1;

(EXPR)      (EXPR)    
----------  ----------

        10           4
        10           9
        10          17

--- 3 row(s) selected.
>>select b,char_length(b),'',char_length('') from t012ut1;

B                     (EXPR)      (EXPR)  (EXPR)    
--------------------  ----------  ------  ----------

abcdef                        10                   0
ABCDEF                        10                   0
   abc                        10                   0
test                          10                   0

--- 4 row(s) selected.
>>select * from t012ut1 where char_length(c) = 4;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        

--- 2 row(s) selected.
>>select * from t012ut1 where char_length(b) + 5 = 15;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        
         30     abc                   def                                                      
         40  test                  trim on non-space                                           

--- 4 row(s) selected.
>>select * from t012ut1 where char_length(c) <> 4;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         30     abc                   def                                                      
         40  test                  trim on non-space                                           

--- 2 row(s) selected.
>>select * from t012ut1 where char_length(c) > 4;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         30     abc                   def                                                      
         40  test                  trim on non-space                                           

--- 2 row(s) selected.
>>select * from t012ut1 where char_length(c) >= 4;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        
         30     abc                   def                                                      
         40  test                  trim on non-space                                           

--- 4 row(s) selected.
>>select * from t012ut1 where char_length(c) < 5;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        

--- 2 row(s) selected.
>>select * from t012ut1 where char_length(c) <= 4;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        

--- 2 row(s) selected.
>>?ifMX
>>
>>-- testing OCTET_LENGTH function
>>select b,octet_length(b),c,octet_length(c) from t012t1;

B           (EXPR)      C                               (EXPR)    
----------  ----------  ------------------------------  ----------

abcdef              10  ghij                                     4
ABCDEF              10  GHIJ                                     4
   abc              10     def                                   9
test                10  trim on non-space                       17

--- 4 row(s) selected.
>>select b,octet_length(b),'',octet_length('') from t012t1;

B           (EXPR)      (EXPR)  (EXPR)    
----------  ----------  ------  ----------

abcdef              10                   0
ABCDEF              10                   0
   abc              10                   0
test                10                   0

--- 4 row(s) selected.
>>select * from t012t1 where octet_length(c) = 4;

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          

--- 2 row(s) selected.
>>select * from t012t1 where octet_length(b) + 5 = 15;

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          
         30     abc         def                        
         40  test        trim on non-space             

--- 4 row(s) selected.
>>select * from t012t1 where octet_length(c) <> 4;

A            B           C                             
-----------  ----------  ------------------------------

         30     abc         def                        
         40  test        trim on non-space             

--- 2 row(s) selected.
>>select * from t012t1 where octet_length(c) > 4;

A            B           C                             
-----------  ----------  ------------------------------

         30     abc         def                        
         40  test        trim on non-space             

--- 2 row(s) selected.
>>select * from t012t1 where octet_length(c) >= 4;

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          
         30     abc         def                        
         40  test        trim on non-space             

--- 4 row(s) selected.
>>select * from t012t1 where octet_length(c) < 5;

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          

--- 2 row(s) selected.
>>select * from t012t1 where octet_length(c) <= 4;

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         20  ABCDEF      GHIJ                          

--- 2 row(s) selected.
>>
>>?ifMX
>>select b,octet_length(b),c,octet_length(c) from t012ut1;

B                     (EXPR)      C                                                             (EXPR)
--------------------  ----------  ------------------------------------------------------------  ----------

abcdef                        20  ghij                                                                   8
ABCDEF                        20  GHIJ                                                                   8
   abc                        20     def                                                                18
test                          20  trim on non-space                                                     34

--- 4 row(s) selected.
>>select b,octet_length(b),'',octet_length('') from t012ut1;

B                     (EXPR)      (EXPR)  (EXPR)    
--------------------  ----------  ------  ----------

abcdef                        20                   0
ABCDEF                        20                   0
   abc                        20                   0
test                          20                   0

--- 4 row(s) selected.
>>select * from t012ut1 where octet_length(c) = 8;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        

--- 2 row(s) selected.
>>select * from t012ut1 where octet_length(b) + 5 = 25;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        
         30     abc                   def                                                      
         40  test                  trim on non-space                                           

--- 4 row(s) selected.
>>select * from t012ut1 where octet_length(c) <> 8;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         30     abc                   def                                                      
         40  test                  trim on non-space                                           

--- 2 row(s) selected.
>>select * from t012ut1 where octet_length(c) > 8;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         30     abc                   def                                                      
         40  test                  trim on non-space                                           

--- 2 row(s) selected.
>>select * from t012ut1 where octet_length(c) >= 8;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        
         30     abc                   def                                                      
         40  test                  trim on non-space                                           

--- 4 row(s) selected.
>>select * from t012ut1 where octet_length(c) < 10;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        

--- 2 row(s) selected.
>>select * from t012ut1 where octet_length(c) <= 8;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        

--- 2 row(s) selected.
>>?ifMX
>>
>>-- testing POSITION function
>>-- tests moved to TEST038(Core) marked "-- *"
>>select b, position('ab' in b) from t012t1;

B           (EXPR)    
----------  ----------

abcdef               1
ABCDEF               0
   abc               4
test                 0

--- 4 row(s) selected.
>>-- * select b, position('xy' in b) from t012t1;
>>select b, position('ef' in b) from t012t1;

B           (EXPR)    
----------  ----------

abcdef               5
ABCDEF               0
   abc               0
test                 0

--- 4 row(s) selected.
>>-- * select b, position('f' in b) from t012t1;
>>-- * select c, position('hi' in c) from t012t1;
>>-- * select b, position('' in b), c, position('' in c) from t012t1;
>>-- * select b, position('' in '') from t012t1;
>>select * from t012t1 where position('b' in b) = 2;

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          

--- 1 row(s) selected.
>>select * from t012t1 where position('b' in b) <> 0;

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         30     abc         def                        

--- 2 row(s) selected.
>>select * from t012t1 where position('b' in b) > 1;

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         30     abc         def                        

--- 2 row(s) selected.
>>select * from t012t1 where position('b' in b) >= 2;

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          
         30     abc         def                        

--- 2 row(s) selected.
>>-- * select * from t012t1 where position('b' in b) < 3;
>>-- * select * from t012t1 where position('b' in b) <= 2;
>>
>>select b, position(N'ab' in b) from t012ut1;

B                     (EXPR)    
--------------------  ----------

abcdef                         1
ABCDEF                         0
   abc                         4
test                           0

--- 4 row(s) selected.
>>select b, position(N'xy' in b) from t012ut1;

B                     (EXPR)    
--------------------  ----------

abcdef                         0
ABCDEF                         0
   abc                         0
test                           0

--- 4 row(s) selected.
>>select b, position(N'ef' in b) from t012ut1;

B                     (EXPR)    
--------------------  ----------

abcdef                         5
ABCDEF                         0
   abc                         0
test                           0

--- 4 row(s) selected.
>>select b, position(N'f' in b) from t012ut1;

B                     (EXPR)    
--------------------  ----------

abcdef                         6
ABCDEF                         0
   abc                         0
test                           0

--- 4 row(s) selected.
>>select c, position(N'hi' in c) from t012ut1;

C                                                             (EXPR)    
------------------------------------------------------------  ----------

ghij                                                                   2
GHIJ                                                                   0
   def                                                                 0
trim on non-space                                                      0

--- 4 row(s) selected.
>>select b, position(N'' in b), c, position(N'' in c) from t012ut1;

B                     (EXPR)      C                                                             (EXPR)
--------------------  ----------  ------------------------------------------------------------  ----------

abcdef                         1  ghij                                                                   1
ABCDEF                         1  GHIJ                                                                   1
   abc                         1     def                                                                 1
test                           1  trim on non-space                                                      1

--- 4 row(s) selected.
>>select b, position(N'' in N'') from t012ut1;

B                     (EXPR)    
--------------------  ----------

abcdef                         1
ABCDEF                         1
   abc                         1
test                           1

--- 4 row(s) selected.
>>select * from t012ut1 where position(N'b' in b) = 2;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        

--- 1 row(s) selected.
>>select * from t012ut1 where position(N'b' in b) <> 0;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         30     abc                   def                                                      

--- 2 row(s) selected.
>>select * from t012ut1 where position(N'b' in b) > 1;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         30     abc                   def                                                      

--- 2 row(s) selected.
>>select * from t012ut1 where position(N'b' in b) >= 2;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         30     abc                   def                                                      

--- 2 row(s) selected.
>>select * from t012ut1 where position(N'b' in b) < 3;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        
         40  test                  trim on non-space                                           

--- 3 row(s) selected.
>>select * from t012ut1 where position(N'b' in b) <= 2;

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        
         20  ABCDEF                GHIJ                                                        
         40  test                  trim on non-space                                           

--- 3 row(s) selected.
>>
>>-- test INSTR function
>>select instr('heelplo', 'l', 2, 2) from dual;

(EXPR)    
----------

         6

--- 1 row(s) selected.
>>select instr('heelplo', 'l', 2) from dual;

(EXPR)    
----------

         4

--- 1 row(s) selected.
>>select instr('heelplo', 'l', 1) from dual;

(EXPR)    
----------

         4

--- 1 row(s) selected.
>>select instr('heelplo', 'l', 5) from dual;

(EXPR)    
----------

         6

--- 1 row(s) selected.
>>select instr('heelplo', 'l', 10) from dual;

(EXPR)    
----------

         0

--- 1 row(s) selected.
>>SELECT INSTR('CORPORATE FLOOR','OR', 3, 1) FROM DUAL;

(EXPR)    
----------

         5

--- 1 row(s) selected.
>>SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) FROM DUAL;

(EXPR)    
----------

        14

--- 1 row(s) selected.
>>SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) FROM DUAL;

*** ERROR[1572] START POSITION value cannot be a negative number for INSTR function.

--- 0 row(s) selected.
>>SELECT INSTR('CORPORATE FLOOR','OR', 3, -2) FROM DUAL;

*** ERROR[1572] OCCURRENCE value cannot be a negative number for INSTR function.

--- 0 row(s) selected.
>>
>>select instr(_ucs2 x'c231 c232 c232 c233 c234 c233 c235', _ucs2 x'c233', 2, 2) from dual;

(EXPR)    
----------

         6

--- 1 row(s) selected.
>>select instr(_ucs2 x'c231 c232 c232 c233 c234 c233 c235', _ucs2 x'c233', 2) from dual;

(EXPR)    
----------

         4

--- 1 row(s) selected.
>>select instr(_ucs2 x'c231 c232 c232 c233 c234 c233 c235', _ucs2 x'c233', 1) from dual;

(EXPR)    
----------

         4

--- 1 row(s) selected.
>>select instr(_ucs2 x'c231 c232 c232 c233 c234 c233 c235', _ucs2 x'c233', 5) from dual;

(EXPR)    
----------

         6

--- 1 row(s) selected.
>>select instr(_ucs2 x'c231 c232 c232 c233 c234 c233 c235', _ucs2 x'c233', 10) from dual;

(EXPR)    
----------

         0

--- 1 row(s) selected.
>>
>>select instr(_ucs2 x'c231 c232 c233 c234 c232 c233 c235 c236 c237 c331 c332 c333 c232 c232 c233', _ucs2 x'c232 c233', 3, 1) from dual;

(EXPR)    
----------

         5

--- 1 row(s) selected.
>>select instr(_ucs2 x'c231 c232 c233 c234 c232 c233 c235 c236 c237 c331 c332 c333 c232 c232 c233', _ucs2 x'c232 c233', 3, 2) from dual;

(EXPR)    
----------

        14

--- 1 row(s) selected.
>>select instr(_ucs2 x'c231 c232 c233 c234 c232 c233 c235 c236 c237 c331 c332 c333 c232 c232 c233', _ucs2 x'c232 c233', -3, 2) from dual;

*** ERROR[1572] START POSITION value cannot be a negative number for INSTR function.

--- 0 row(s) selected.
>>
>>
>>-- testing SUBSTRING function
>>select b,substring(b from 2 for 1) as u,char_length(substring(b from 2 for 1)) len from t012t1;

B           U  LEN       
----------  -  ----------

abcdef      b           1
ABCDEF      B           1
   abc                  1
test        e           1

--- 4 row(s) selected.
>>select b,substring(b from 2),char_length(substring(b from 2)) from t012t1;

B           (EXPR)     (EXPR)    
----------  ---------  ----------

abcdef      bcdef               9
ABCDEF      BCDEF               9
   abc        abc               9
test        est                 9

--- 4 row(s) selected.
>>select c,substring(c from 2 for 1),char_length(substring(c from 2 for 1)) from t012t1;

C                               (EXPR)  (EXPR)    
------------------------------  ------  ----------

ghij                            h                1
GHIJ                            H                1
   def                                           1
trim on non-space               r                1

--- 4 row(s) selected.
>>select c,substring(c from 2),char_length(substring(c from 2)) from t012t1;

C                               (EXPR)                         (EXPR)
------------------------------  -----------------------------  ----------

ghij                            hij                                     3
GHIJ                            HIJ                                     3
   def                            def                                   8
trim on non-space               rim on non-space                       16

--- 4 row(s) selected.
>>select b,substring(b from 6 for 1),char_length(substring(b from 6 for 1)) from t012t1;

B           (EXPR)  (EXPR)    
----------  ------  ----------

abcdef      f                1
ABCDEF      F                1
   abc      c                1
test                         1

--- 4 row(s) selected.
>>select c,substring(c from 2 for 3),char_length(substring(c from 2 for 3)) from t012t1;

C                               (EXPR)  (EXPR)    
------------------------------  ------  ----------

ghij                            hij              3
GHIJ                            HIJ              3
   def                            d              3
trim on non-space               rim              3

--- 4 row(s) selected.
>>select b,substring(b from 2 for 4),char_length(substring(b from 2 for 4)) from t012t1;

B           (EXPR)  (EXPR)    
----------  ------  ----------

abcdef      bcde             4
ABCDEF      BCDE             4
   abc        ab             4
test        est              4

--- 4 row(s) selected.
>>select c,substring(c from 2 for 5),char_length(substring(c from 2 for 5)) from t012t1;

C                               (EXPR)  (EXPR)    
------------------------------  ------  ----------

ghij                            hij              3
GHIJ                            HIJ              3
   def                            def            5
trim on non-space               rim o            5

--- 4 row(s) selected.
>>select b,substring(b from 2 for 0),char_length(substring(b from 2 for 0)) from t012t1;

B           (EXPR)  (EXPR)    
----------  ------  ----------

abcdef                       0
ABCDEF                       0
   abc                       0
test                         0

--- 4 row(s) selected.
>>select b,substring(b from -8 for 2),char_length(substring(b from -8 for 2)) from t012t1;

B           (EXPR)  (EXPR)    
----------  ------  ----------

abcdef                       0
ABCDEF                       0
   abc                       0
test                         0

--- 4 row(s) selected.
>>select c,substring(c from -2 for 6),char_length(substring(c from -2 for 6)) from t012t1;

C                               (EXPR)  (EXPR)    
------------------------------  ------  ----------

ghij                            ghi              3
GHIJ                            GHI              3
   def                                           3
trim on non-space               tri              3

--- 4 row(s) selected.
>>select c,substring(c from -1 for 6),char_length(substring(c from -1 for 6)) from t012t1;

C                               (EXPR)  (EXPR)    
------------------------------  ------  ----------

ghij                            ghij             4
GHIJ                            GHIJ             4
   def                             d             4
trim on non-space               trim             4

--- 4 row(s) selected.
>>select c,substring(c from 0 for 6),char_length(substring(c from 0 for 6)) from t012t1;

C                               (EXPR)  (EXPR)    
------------------------------  ------  ----------

ghij                            ghij             4
GHIJ                            GHIJ             4
   def                             de            5
trim on non-space               trim             5

--- 4 row(s) selected.
>>select c,substring(c from 1 for 6),char_length(substring(c from 1 for 6)) from t012t1;

C                               (EXPR)  (EXPR)    
------------------------------  ------  ----------

ghij                            ghij             4
GHIJ                            GHIJ             4
   def                             def           6
trim on non-space               trim o           6

--- 4 row(s) selected.
>>select c,substring(c from 7 for 3),char_length(substring(c from 7 for 3)) from t012t1;

C                               (EXPR)  (EXPR)    
------------------------------  ------  ----------

ghij                                             0
GHIJ                                             0
   def                                           3
trim on non-space               n n              3

--- 4 row(s) selected.
>>select b,substring(b from 2),substring(substring(b from 2) from 2) from t012t1;

B           (EXPR)     (EXPR)  
----------  ---------  --------

abcdef      bcdef      cdef    
ABCDEF      BCDEF      CDEF    
   abc        abc       abc    
test        est        st      

--- 4 row(s) selected.
>>select b,substring('' from 1 for 2),char_length(substring('' from 1 for 2)) from t012t1;

B           (EXPR)  (EXPR)    
----------  ------  ----------

abcdef                       0
ABCDEF                       0
   abc                       0
test                         0

--- 4 row(s) selected.
>>select * from t012t1 where substring(c from 2) = 'hij';

A            B           C                             
-----------  ----------  ------------------------------

         10  abcdef      ghij                          

--- 1 row(s) selected.
>>select * from t012t1 where substring(c from 2) <> 'hij';

A            B           C                             
-----------  ----------  ------------------------------

         20  ABCDEF      GHIJ                          
         30     abc         def                        
         40  test        trim on non-space             

--- 3 row(s) selected.
>>-- The following query should raise a SQL exception. (To be implemented)
>>-- select b,substring(b from 2 for -2) from t012t1;
>>
>>?ifMX
>>select b,substring(b from 2 for 1),char_length(substring(b from 2 for 1)) from t012ut1;

B                     (EXPR)  (EXPR)    
--------------------  ------  ----------

abcdef                b                1
ABCDEF                B                1
   abc                                 1
test                  e                1

--- 4 row(s) selected.
>>select b,substring(b from 2),char_length(substring(b from 2)) from t012ut1;

B                     (EXPR)              (EXPR)    
--------------------  ------------------  ----------

abcdef                bcdef                        9
ABCDEF                BCDEF                        9
   abc                  abc                        9
test                  est                          9

--- 4 row(s) selected.
>>select c,substring(c from 2 for 1),char_length(substring(c from 2 for 1)) from t012ut1;

C                                                             (EXPR)  (EXPR)
------------------------------------------------------------  ------  ----------

ghij                                                          h                1
GHIJ                                                          H                1
   def                                                                         1
trim on non-space                                             r                1

--- 4 row(s) selected.
>>select c,substring(c from 2),char_length(substring(c from 2)) from t012ut1;

C                                                             (EXPR)                                                      (EXPR)
------------------------------------------------------------  ----------------------------------------------------------  ----------

ghij                                                          hij                                                                  3
GHIJ                                                          HIJ                                                                  3
   def                                                          def                                                                8
trim on non-space                                             rim on non-space                                                    16

--- 4 row(s) selected.
>>select b,substring(b from 6 for 1),char_length(substring(b from 6 for 1)) from t012ut1;

B                     (EXPR)  (EXPR)    
--------------------  ------  ----------

abcdef                f                1
ABCDEF                F                1
   abc                c                1
test                                   1

--- 4 row(s) selected.
>>select c,substring(c from 2 for 3),char_length(substring(c from 2 for 3)) from t012ut1;

C                                                             (EXPR)  (EXPR)
------------------------------------------------------------  ------  ----------

ghij                                                          hij              3
GHIJ                                                          HIJ              3
   def                                                          d              3
trim on non-space                                             rim              3

--- 4 row(s) selected.
>>select b,substring(b from 2 for 4),char_length(substring(b from 2 for 4)) from t012ut1;

B                     (EXPR)    (EXPR)    
--------------------  --------  ----------

abcdef                bcde               4
ABCDEF                BCDE               4
   abc                  ab               4
test                  est                4

--- 4 row(s) selected.
>>select c,substring(c from 2 for 5),char_length(substring(c from 2 for 5)) from t012ut1;

C                                                             (EXPR)      (EXPR)
------------------------------------------------------------  ----------  ----------

ghij                                                          hij                  3
GHIJ                                                          HIJ                  3
   def                                                          def                5
trim on non-space                                             rim o                5

--- 4 row(s) selected.
>>select b,substring(b from 2 for 0),char_length(substring(b from 2 for 0)) from t012ut1;

B                     (EXPR)  (EXPR)    
--------------------  ------  ----------

abcdef                                 0
ABCDEF                                 0
   abc                                 0
test                                   0

--- 4 row(s) selected.
>>select b,substring(b from -8 for 2),char_length(substring(b from -8 for 2)) from t012ut1;

B                     (EXPR)  (EXPR)    
--------------------  ------  ----------

abcdef                                 0
ABCDEF                                 0
   abc                                 0
test                                   0

--- 4 row(s) selected.
>>select c,substring(c from -2 for 6),char_length(substring(c from -2 for 6)) from t012ut1;

C                                                             (EXPR)        (EXPR)
------------------------------------------------------------  ------------  ----------

ghij                                                          ghi                    3
GHIJ                                                          GHI                    3
   def                                                                               3
trim on non-space                                             tri                    3

--- 4 row(s) selected.
>>select c,substring(c from -1 for 6),char_length(substring(c from -1 for 6)) from t012ut1;

C                                                             (EXPR)        (EXPR)
------------------------------------------------------------  ------------  ----------

ghij                                                          ghij                   4
GHIJ                                                          GHIJ                   4
   def                                                           d                   4
trim on non-space                                             trim                   4

--- 4 row(s) selected.
>>select c,substring(c from 0 for 6),char_length(substring(c from 0 for 6)) from t012ut1;

C                                                             (EXPR)        (EXPR)
------------------------------------------------------------  ------------  ----------

ghij                                                          ghij                   4
GHIJ                                                          GHIJ                   4
   def                                                           de                  5
trim on non-space                                             trim                   5

--- 4 row(s) selected.
>>select c,substring(c from 1 for 6),char_length(substring(c from 1 for 6)) from t012ut1;

C                                                             (EXPR)        (EXPR)
------------------------------------------------------------  ------------  ----------

ghij                                                          ghij                   4
GHIJ                                                          GHIJ                   4
   def                                                           def                 6
trim on non-space                                             trim o                 6

--- 4 row(s) selected.
>>select c,substring(c from 7 for 3),char_length(substring(c from 7 for 3)) from t012ut1;

C                                                             (EXPR)  (EXPR)
------------------------------------------------------------  ------  ----------

ghij                                                                           0
GHIJ                                                                           0
   def                                                                         3
trim on non-space                                             n n              3

--- 4 row(s) selected.
>>select b,substring(b from 2),substring(substring(b from 2) from 2) from t012ut1;

B                     (EXPR)              (EXPR)          
--------------------  ------------------  ----------------

abcdef                bcdef               cdef            
ABCDEF                BCDEF               CDEF            
   abc                  abc                abc            
test                  est                 st              

--- 4 row(s) selected.
>>select b,substring(N'' from 1 for 2),char_length(substring(N'' from 1 for 2)) from t012ut1;

B                     (EXPR)  (EXPR)    
--------------------  ------  ----------

abcdef                                 0
ABCDEF                                 0
   abc                                 0
test                                   0

--- 4 row(s) selected.
>>select * from t012ut1 where substring(c from 2) = N'hij';

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         10  abcdef                ghij                                                        

--- 1 row(s) selected.
>>select * from t012ut1 where substring(c from 2) <> N'hij';

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         20  ABCDEF                GHIJ                                                        
         30     abc                   def                                                      
         40  test                  trim on non-space                                           

--- 3 row(s) selected.
>>-- The following query should raise a SQL exception. (To be implemented)
>>-- select b,substring(b from 2 for -2) from t012ut1;
>>?ifMX
>>
>>-- testing TRIM function
>>-- tests moved to TEST038(Core) marked "-- *"
>>select b,trim(leading from b),char_length(trim(leading from b)) from t012t1;

B           (EXPR)      (EXPR)    
----------  ----------  ----------

abcdef      abcdef              10
ABCDEF      ABCDEF              10
   abc      abc                  7
test        test                10

--- 4 row(s) selected.
>>-- * select c,trim(leading from c),char_length(trim(leading from c)) from t012t1;
>>-- * select b,trim(leading ' ' from b),char_length(trim(leading ' ' from b)) from t012t1;
>>select c,trim(leading ' ' from c),char_length(trim(leading ' ' from c)) from t012t1;

C                               (EXPR)                          (EXPR)
------------------------------  ------------------------------  ----------

ghij                            ghij                                     4
GHIJ                            GHIJ                                     4
   def                          def                                      6
trim on non-space               trim on non-space                       17

--- 4 row(s) selected.
>>-- * select b,trim(trailing from b),char_length(trim(trailing from b)) from t012t1;
>>select c,trim(trailing from c),char_length(trim(trailing from c)) from t012t1;

C                               (EXPR)                          (EXPR)
------------------------------  ------------------------------  ----------

ghij                            ghij                                     4
GHIJ                            GHIJ                                     4
   def                             def                                   6
trim on non-space               trim on non-space                       17

--- 4 row(s) selected.
>>-- * select b,trim(both from b),char_length(trim(both from b)) from t012t1;
>>select c,trim(both from c),char_length(trim(both from c)) from t012t1;

C                               (EXPR)                          (EXPR)
------------------------------  ------------------------------  ----------

ghij                            ghij                                     4
GHIJ                            GHIJ                                     4
   def                          def                                      3
trim on non-space               trim on non-space                       17

--- 4 row(s) selected.
>>select b,trim(b),char_length(trim(b)) from t012t1;

B           (EXPR)      (EXPR)    
----------  ----------  ----------

abcdef      abcdef               6
ABCDEF      ABCDEF               6
   abc      abc                  3
test        test                 4

--- 4 row(s) selected.
>>select c,trim(c),char_length(trim(c)) from t012t1;

C                               (EXPR)                          (EXPR)
------------------------------  ------------------------------  ----------

ghij                            ghij                                     4
GHIJ                            GHIJ                                     4
   def                          def                                      3
trim on non-space               trim on non-space                       17

--- 4 row(s) selected.
>>select b,trim('a' from b),char_length(trim('a' from b)) from t012t1;

B           (EXPR)      (EXPR)    
----------  ----------  ----------

abcdef      bcdef                9
ABCDEF      ABCDEF              10
   abc         abc              10
test        test                10

--- 4 row(s) selected.
>>-- * select b,trim('b' from trim('a' from b)),char_length(trim('b' from trim('a' from b))) from t012t1;
>>select b,trim('t' from b),char_length(trim('t' from b)) from t012t1;

B           (EXPR)      (EXPR)    
----------  ----------  ----------

abcdef      abcdef              10
ABCDEF      ABCDEF              10
   abc         abc              10
test        est                  9

--- 4 row(s) selected.
>>-- * select * from t012t1 where trim(c)='def';
>>-- * select * from t012t1 where trim(leading from c) = 'def';
>>-- The following query should raise a SQL exception.(To be implemented)  
>>-- select b,trim('ab' from b),c from t012t1;
>>
>>?ifMX
>>select b,trim(leading from b),char_length(trim(leading from b)) from t012ut1;

B                     (EXPR)                (EXPR)    
--------------------  --------------------  ----------

abcdef                abcdef                        10
ABCDEF                ABCDEF                        10
   abc                abc                            7
test                  test                          10

--- 4 row(s) selected.
>>select c,trim(leading from c),char_length(trim(leading from c)) from t012ut1;

C                                                             (EXPR)                                                        (EXPR)
------------------------------------------------------------  ------------------------------------------------------------  ----------

ghij                                                          ghij                                                                   4
GHIJ                                                          GHIJ                                                                   4
   def                                                        def                                                                    6
trim on non-space                                             trim on non-space                                                     17

--- 4 row(s) selected.
>>select b,trim(leading N' ' from b),char_length(trim(leading N' ' from b)) from t012ut1;

B                     (EXPR)                (EXPR)    
--------------------  --------------------  ----------

abcdef                abcdef                        10
ABCDEF                ABCDEF                        10
   abc                abc                            7
test                  test                          10

--- 4 row(s) selected.
>>select c,trim(leading N' ' from c),char_length(trim(leading N' ' from c)) from t012ut1;

C                                                             (EXPR)                                                        (EXPR)
------------------------------------------------------------  ------------------------------------------------------------  ----------

ghij                                                          ghij                                                                   4
GHIJ                                                          GHIJ                                                                   4
   def                                                        def                                                                    6
trim on non-space                                             trim on non-space                                                     17

--- 4 row(s) selected.
>>select b,trim(trailing from b),char_length(trim(trailing from b)) from t012ut1;

B                     (EXPR)                (EXPR)    
--------------------  --------------------  ----------

abcdef                abcdef                         6
ABCDEF                ABCDEF                         6
   abc                   abc                         6
test                  test                           4

--- 4 row(s) selected.
>>select c,trim(trailing from c),char_length(trim(trailing from c)) from t012ut1;

C                                                             (EXPR)                                                        (EXPR)
------------------------------------------------------------  ------------------------------------------------------------  ----------

ghij                                                          ghij                                                                   4
GHIJ                                                          GHIJ                                                                   4
   def                                                           def                                                                 6
trim on non-space                                             trim on non-space                                                     17

--- 4 row(s) selected.
>>select b,trim(both from b),char_length(trim(both from b)) from t012ut1;

B                     (EXPR)                (EXPR)    
--------------------  --------------------  ----------

abcdef                abcdef                         6
ABCDEF                ABCDEF                         6
   abc                abc                            3
test                  test                           4

--- 4 row(s) selected.
>>select c,trim(both from c),char_length(trim(both from c)) from t012ut1;

C                                                             (EXPR)                                                        (EXPR)
------------------------------------------------------------  ------------------------------------------------------------  ----------

ghij                                                          ghij                                                                   4
GHIJ                                                          GHIJ                                                                   4
   def                                                        def                                                                    3
trim on non-space                                             trim on non-space                                                     17

--- 4 row(s) selected.
>>select b,trim(b),char_length(trim(b)) from t012ut1;

B                     (EXPR)                (EXPR)    
--------------------  --------------------  ----------

abcdef                abcdef                         6
ABCDEF                ABCDEF                         6
   abc                abc                            3
test                  test                           4

--- 4 row(s) selected.
>>select c,trim(c),char_length(trim(c)) from t012ut1;

C                                                             (EXPR)                                                        (EXPR)
------------------------------------------------------------  ------------------------------------------------------------  ----------

ghij                                                          ghij                                                                   4
GHIJ                                                          GHIJ                                                                   4
   def                                                        def                                                                    3
trim on non-space                                             trim on non-space                                                     17

--- 4 row(s) selected.
>>select b,trim(N'a' from b),char_length(trim(N'a' from b)) from t012ut1;

B                     (EXPR)                (EXPR)    
--------------------  --------------------  ----------

abcdef                bcdef                          9
ABCDEF                ABCDEF                        10
   abc                   abc                        10
test                  test                          10

--- 4 row(s) selected.
>>select b,trim(N'b' from trim(N'a' from b)),char_length(trim(N'b' from trim(N'a' from b))) from t012ut1;

B                     (EXPR)                (EXPR)    
--------------------  --------------------  ----------

abcdef                cdef                           8
ABCDEF                ABCDEF                        10
   abc                   abc                        10
test                  test                          10

--- 4 row(s) selected.
>>select b,trim(N't' from b),char_length(trim(N't' from b)) from t012ut1;

B                     (EXPR)                (EXPR)    
--------------------  --------------------  ----------

abcdef                abcdef                        10
ABCDEF                ABCDEF                        10
   abc                   abc                        10
test                  est                            9

--- 4 row(s) selected.
>>select * from t012ut1 where trim(c)=N'def';

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         30     abc                   def                                                      

--- 1 row(s) selected.
>>select * from t012ut1 where trim(leading from c) = N'def';

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         30     abc                   def                                                      

--- 1 row(s) selected.
>>-- The following query should raise a SQL exception.(To be implemented)  
>>-- select b,trim(N'ab' from b),c from t012ut1;
>>?ifMX
>>
>>-- test CONCAT operation
>>select b||b,char_length(b||b) from t012t1;

(EXPR)                (EXPR)    
--------------------  ----------

abcdef    abcdef              20
ABCDEF    ABCDEF              20
   abc       abc              20
test      test                20

--- 4 row(s) selected.
>>select b||trim(b),char_length(b||trim(b)) from t012t1;

(EXPR)                (EXPR)    
--------------------  ----------

abcdef    abcdef              16
ABCDEF    ABCDEF              16
   abc    abc                 13
test      test                14

--- 4 row(s) selected.
>>select trim(b)||trim(b),char_length(trim(b)||trim(b)) from t012t1;

(EXPR)                (EXPR)    
--------------------  ----------

abcdefabcdef                  12
ABCDEFABCDEF                  12
abcabc                         6
testtest                       8

--- 4 row(s) selected.
>>select b||'',char_length(b||'') from t012t1;

(EXPR)      (EXPR)    
----------  ----------

abcdef              10
ABCDEF              10
   abc              10
test                10

--- 4 row(s) selected.
>>select b||'1234',char_length(b||'1234') from t012t1;

(EXPR)          (EXPR)    
--------------  ----------

abcdef    1234          14
ABCDEF    1234          14
   abc    1234          14
test      1234          14

--- 4 row(s) selected.
>>select '1234'||b,char_length('1234'||b) from t012t1;

(EXPR)          (EXPR)    
--------------  ----------

1234abcdef              14
1234ABCDEF              14
1234   abc              14
1234test                14

--- 4 row(s) selected.
>>select b, '1234'||'5678' from t012t1;

B           (EXPR)  
----------  --------

abcdef      12345678
ABCDEF      12345678
   abc      12345678
test        12345678

--- 4 row(s) selected.
>>select b, '1234'||''||'5678' from t012t1;

B           (EXPR)  
----------  --------

abcdef      12345678
ABCDEF      12345678
   abc      12345678
test        12345678

--- 4 row(s) selected.
>>select b, '1234'||' '||'5678' from t012t1;

B           (EXPR)   
----------  ---------

abcdef      1234 5678
ABCDEF      1234 5678
   abc      1234 5678
test        1234 5678

--- 4 row(s) selected.
>>select c||c,char_length(c||c) from t012t1;

(EXPR)                                                        (EXPR)    
------------------------------------------------------------  ----------

ghijghij                                                               8
GHIJGHIJ                                                               8
   def      def                                                       18
trim on non-spacetrim on non-space                                    34

--- 4 row(s) selected.
>>select c||trim(c),char_length(c||trim(c)) from t012t1;

(EXPR)                                                        (EXPR)    
------------------------------------------------------------  ----------

ghijghij                                                               8
GHIJGHIJ                                                               8
   def   def                                                          12
trim on non-spacetrim on non-space                                    34

--- 4 row(s) selected.
>>select trim(c)||trim(c),char_length(trim(c)||trim(c)) from t012t1;

(EXPR)                                                        (EXPR)    
------------------------------------------------------------  ----------

ghijghij                                                               8
GHIJGHIJ                                                               8
defdef                                                                 6
trim on non-spacetrim on non-space                                    34

--- 4 row(s) selected.
>>select c||'',char_length(c||'') from t012t1;

(EXPR)                          (EXPR)    
------------------------------  ----------

ghij                                     4
GHIJ                                     4
   def                                   9
trim on non-space                       17

--- 4 row(s) selected.
>>select c||'1234',char_length(c||'1234') from t012t1;

(EXPR)                              (EXPR)    
----------------------------------  ----------

ghij1234                                     8
GHIJ1234                                     8
   def   1234                               13
trim on non-space1234                       21

--- 4 row(s) selected.
>>select '1234'||c,char_length('1234'||c) from t012t1;

(EXPR)                              (EXPR)    
----------------------------------  ----------

1234ghij                                     8
1234GHIJ                                     8
1234   def                                  13
1234trim on non-space                       21

--- 4 row(s) selected.
>>select b||c,char_length(b||c) from t012t1;

(EXPR)                                    (EXPR)    
----------------------------------------  ----------

abcdef    ghij                                    14
ABCDEF    GHIJ                                    14
   abc       def                                  19
test      trim on non-space                       27

--- 4 row(s) selected.
>>select trim(b)||trim(c),char_length(trim(b)||trim(c)) from t012t1;

(EXPR)                                    (EXPR)    
----------------------------------------  ----------

abcdefghij                                        10
ABCDEFGHIJ                                        10
abcdef                                             6
testtrim on non-space                             21

--- 4 row(s) selected.
>>select trim(b)||trim(c)||trim(b),char_length(trim(b)||trim(c)||trim(b)) from t012t1;

(EXPR)                                              (EXPR)    
--------------------------------------------------  ----------

abcdefghijabcdef                                            16
ABCDEFGHIJABCDEF                                            16
abcdefabc                                                    9
testtrim on non-spacetest                                   25

--- 4 row(s) selected.
>>select * from t012t1 where trim(b)||trim(c) = 'abcdef';

A            B           C                             
-----------  ----------  ------------------------------

         30     abc         def                        

--- 1 row(s) selected.
>>
>>select b||b,char_length(b||b) from t012ut1;

(EXPR)                                    (EXPR)    
----------------------------------------  ----------

abcdef    abcdef                                  20
ABCDEF    ABCDEF                                  20
   abc       abc                                  20
test      test                                    20

--- 4 row(s) selected.
>>select b||trim(b),char_length(b||trim(b)) from t012ut1;

(EXPR)                                    (EXPR)    
----------------------------------------  ----------

abcdef    abcdef                                  16
ABCDEF    ABCDEF                                  16
   abc    abc                                     13
test      test                                    14

--- 4 row(s) selected.
>>select trim(b)||trim(b),char_length(trim(b)||trim(b)) from t012ut1;

(EXPR)                                    (EXPR)    
----------------------------------------  ----------

abcdefabcdef                                      12
ABCDEFABCDEF                                      12
abcabc                                             6
testtest                                           8

--- 4 row(s) selected.
>>select b||N'',char_length(b||N'') from t012ut1;

(EXPR)                (EXPR)    
--------------------  ----------

abcdef                        10
ABCDEF                        10
   abc                        10
test                          10

--- 4 row(s) selected.
>>select b||N'1234',char_length(b||N'1234') from t012ut1;

(EXPR)                        (EXPR)    
----------------------------  ----------

abcdef    1234                        14
ABCDEF    1234                        14
   abc    1234                        14
test      1234                        14

--- 4 row(s) selected.
>>select N'1234'||b,char_length(N'1234'||b) from t012ut1;

(EXPR)                        (EXPR)    
----------------------------  ----------

1234abcdef                            14
1234ABCDEF                            14
1234   abc                            14
1234test                              14

--- 4 row(s) selected.
>>select b, N'1234'||N'5678' from t012ut1;

B                     (EXPR)          
--------------------  ----------------

abcdef                12345678        
ABCDEF                12345678        
   abc                12345678        
test                  12345678        

--- 4 row(s) selected.
>>select b, N'1234'||N''||N'5678' from t012ut1;

B                     (EXPR)          
--------------------  ----------------

abcdef                12345678        
ABCDEF                12345678        
   abc                12345678        
test                  12345678        

--- 4 row(s) selected.
>>select b, N'1234'||N' '||N'5678' from t012ut1;

B                     (EXPR)            
--------------------  ------------------

abcdef                1234 5678         
ABCDEF                1234 5678         
   abc                1234 5678         
test                  1234 5678         

--- 4 row(s) selected.
>>select c||c,char_length(c||c) from t012ut1;

(EXPR)                                                                                                                    (EXPR)
------------------------------------------------------------------------------------------------------------------------  ----------

ghijghij                                                                                                                           8
GHIJGHIJ                                                                                                                           8
   def      def                                                                                                                   18
trim on non-spacetrim on non-space                                                                                                34

--- 4 row(s) selected.
>>select c||trim(c),char_length(c||trim(c)) from t012ut1;

(EXPR)                                                                                                                    (EXPR)
------------------------------------------------------------------------------------------------------------------------  ----------

ghijghij                                                                                                                           8
GHIJGHIJ                                                                                                                           8
   def   def                                                                                                                      12
trim on non-spacetrim on non-space                                                                                                34

--- 4 row(s) selected.
>>select trim(c)||trim(c),char_length(trim(c)||trim(c)) from t012ut1;

(EXPR)                                                                                                                    (EXPR)
------------------------------------------------------------------------------------------------------------------------  ----------

ghijghij                                                                                                                           8
GHIJGHIJ                                                                                                                           8
defdef                                                                                                                             6
trim on non-spacetrim on non-space                                                                                                34

--- 4 row(s) selected.
>>select c||N'',char_length(c||N'') from t012ut1;

(EXPR)                                                        (EXPR)    
------------------------------------------------------------  ----------

ghij                                                                   4
GHIJ                                                                   4
   def                                                                 9
trim on non-space                                                     17

--- 4 row(s) selected.
>>select c||N'1234',char_length(c||N'1234') from t012ut1;

(EXPR)                                                                (EXPR)
--------------------------------------------------------------------  ----------

ghij1234                                                                       8
GHIJ1234                                                                       8
   def   1234                                                                 13
trim on non-space1234                                                         21

--- 4 row(s) selected.
>>select N'1234'||c,char_length(N'1234'||c) from t012ut1;

(EXPR)                                                                (EXPR)
--------------------------------------------------------------------  ----------

1234ghij                                                                       8
1234GHIJ                                                                       8
1234   def                                                                    13
1234trim on non-space                                                         21

--- 4 row(s) selected.
>>select b||c,char_length(b||c) from t012ut1;

(EXPR)                                                                            (EXPR)
--------------------------------------------------------------------------------  ----------

abcdef    ghij                                                                            14
ABCDEF    GHIJ                                                                            14
   abc       def                                                                          19
test      trim on non-space                                                               27

--- 4 row(s) selected.
>>select trim(b)||trim(c),char_length(trim(b)||trim(c)) from t012ut1;

(EXPR)                                                                            (EXPR)
--------------------------------------------------------------------------------  ----------

abcdefghij                                                                                10
ABCDEFGHIJ                                                                                10
abcdef                                                                                     6
testtrim on non-space                                                                     21

--- 4 row(s) selected.
>>select trim(b)||trim(c)||trim(b),char_length(trim(b)||trim(c)||trim(b)) from t012ut1;

(EXPR)                                                                                                (EXPR)
----------------------------------------------------------------------------------------------------  ----------

abcdefghijabcdef                                                                                              16
ABCDEFGHIJABCDEF                                                                                              16
abcdefabc                                                                                                      9
testtrim on non-spacetest                                                                                     25

--- 4 row(s) selected.
>>select * from t012ut1 where trim(b)||trim(c) = N'abcdef';

A            B                     C
-----------  --------------------  ------------------------------------------------------------

         30     abc                   def                                                      

--- 1 row(s) selected.
>>
>>-- test BIT functions
>>select bitand(1,1), bitor(1,1), bitxor(1,1), bitnot(1) from (values(1)) x(a);

(EXPR)       (EXPR)       (EXPR)       (EXPR)
-----------  -----------  -----------  ------

          1            1            0      -2

--- 1 row(s) selected.
>>select bitand(1,-1), bitor(1,-1), bitxor(-1,1), bitnot(-1) from (values(1)) x(a);

(EXPR)       (EXPR)       (EXPR)       (EXPR)
-----------  -----------  -----------  ------

          1           -1           -2       0

--- 1 row(s) selected.
>>select bitnot(bitnot(1)) from (values(1)) x(a);

(EXPR)
------

     1

--- 1 row(s) selected.
>>select bitand(1,123456789), bitor(1,123456789), bitxor(1,123456789), bitnot(123456789) from (values(1)) x(a);

(EXPR)       (EXPR)       (EXPR)       (EXPR)     
-----------  -----------  -----------  -----------

          1    123456789    123456788   -123456790

--- 1 row(s) selected.
>>select bitand(1,-123456789), bitor(1,-123456789), bitxor(-123456789,1), bitnot(-123456789) from (values(1)) x(a);

(EXPR)                (EXPR)                (EXPR)                (EXPR)
--------------------  --------------------  --------------------  --------------------

                   1            -123456789            -123456790             123456788

--- 1 row(s) selected.
>>
>>select 1 & 1, 1 | 1, 1 ^ 1, ~ 1 from (values(1)) x(a);

(EXPR)       (EXPR)       (EXPR)       (EXPR)     
-----------  -----------  -----------  -----------

          1            1            0           -2

--- 1 row(s) selected.
>>select 1 & 123456789, 1 | 123456789, 1 ^ 123456789, ~ 123456789 from (values(1)) x(a);

(EXPR)       (EXPR)       (EXPR)       (EXPR)     
-----------  -----------  -----------  -----------

          1    123456789    123456788   -123456790

--- 1 row(s) selected.
>>select 1 | 2 | 4 from (values(1)) x(a);

(EXPR)     
-----------

          7

--- 1 row(s) selected.
>>select 1 & 2 & 4 from (values(1)) x(a);

(EXPR)     
-----------

          0

--- 1 row(s) selected.
>>select ~ (1 | 2 | 4) from (values(1)) x(a);

(EXPR)     
-----------

         -8

--- 1 row(s) selected.
>>select ~ (1 ^ 2 ^ 4) from (values(1)) x(a);

(EXPR)     
-----------

         -8

--- 1 row(s) selected.
>>select 1 & bitor(1,1) from (values(1)) x(a);

(EXPR)     
-----------

          1

--- 1 row(s) selected.
>>select 1 & bitxor(1,1) from (values(1)) x(a);

(EXPR)     
-----------

          0

--- 1 row(s) selected.
>>
>>select bitand(a,a), bitand(b,b), bitand(c,c), bitand(d,d),
+>       bitand(e,e), bitand(f,f), bitand(g,g), bitand(h,h),
+>       bitand(i,i), bitand(j,j), bitand(k,k), bitand(l,l),
+>       bitand(m,m), bitand(n,n), bitand(o,o)
+>from t012t3;

(EXPR)       (EXPR)       (EXPR)      (EXPR)      (EXPR)       (EXPR)       (EXPR)      (EXPR)      (EXPR)                (EXPR)       (EXPR)      (EXPR)       (EXPR)      (EXPR)                (EXPR)
-----------  -----------  ----------  ----------  -----------  -----------  ----------  ----------  --------------------  -----------  ----------  -----------  ----------  --------------------  ----------

          0            0           0           0            0            0           0           0                     0            0           0            0           0                     0           0
          1            1           1           1            1            1           1           1                     1            1           1            1           1                     1           1
          3            3           3           3            3            3           3           3                     3            3           3            3           3                     3           3
          4            4           4           4            4            4           4           4                     4            4           4            4           4                     4           4
         -1           -1           1           1           -1           -1           1           1                    -1           -1           1           -1           1                    -1           1
         -3           -3           3           3           -3           -3           3           3                    -3           -3           3           -3           3                    -3           3
         -4           -4           4           4           -4           -4           4           4                    -4           -4           4           -4           4                    -4           4
          0            1           0           1            0            1           0           1                     0            0           1            0           1                     0           1
          0            ?           0           ?            0            ?           0           ?                     0            ?           0            0           ?                     ?           ?

--- 9 row(s) selected.
>>
>>select bitor(a,a), bitor(b,b), bitor(c,c), bitor(d,d),
+>       bitor(e,e), bitor(f,f), bitor(g,g), bitor(h,h),
+>       bitor(i,i), bitor(j,j), bitor(k,k), bitor(l,l),
+>       bitor(m,m), bitor(n,n), bitor(o,o)
+>from t012t3;

(EXPR)       (EXPR)       (EXPR)      (EXPR)      (EXPR)       (EXPR)       (EXPR)      (EXPR)      (EXPR)                (EXPR)       (EXPR)      (EXPR)       (EXPR)      (EXPR)                (EXPR)
-----------  -----------  ----------  ----------  -----------  -----------  ----------  ----------  --------------------  -----------  ----------  -----------  ----------  --------------------  ----------

          0            0           0           0            0            0           0           0                     0            0           0            0           0                     0           0
          1            1           1           1            1            1           1           1                     1            1           1            1           1                     1           1
          3            3           3           3            3            3           3           3                     3            3           3            3           3                     3           3
          4            4           4           4            4            4           4           4                     4            4           4            4           4                     4           4
         -1           -1           1           1           -1           -1           1           1                    -1           -1           1           -1           1                    -1           1
         -3           -3           3           3           -3           -3           3           3                    -3           -3           3           -3           3                    -3           3
         -4           -4           4           4           -4           -4           4           4                    -4           -4           4           -4           4                    -4           4
          0            1           0           1            0            1           0           1                     0            0           1            0           1                     0           1
          0            ?           0           ?            0            ?           0           ?                     0            ?           0            0           ?                     ?           ?

--- 9 row(s) selected.
>>
>>select bitxor(a,a), bitxor(b,b), bitxor(c,c), bitxor(d,d),
+>       bitxor(e,e), bitxor(f,f), bitxor(g,g), bitxor(h,h),
+>       bitxor(i,i), bitxor(j,j), bitxor(k,k), bitxor(l,l),
+>       bitxor(m,m), bitxor(n,n), bitxor(o,o)
+>from t012t3;

(EXPR)       (EXPR)       (EXPR)      (EXPR)      (EXPR)       (EXPR)       (EXPR)      (EXPR)      (EXPR)                (EXPR)       (EXPR)      (EXPR)       (EXPR)      (EXPR)                (EXPR)
-----------  -----------  ----------  ----------  -----------  -----------  ----------  ----------  --------------------  -----------  ----------  -----------  ----------  --------------------  ----------

          0            0           0           0            0            0           0           0                     0            0           0            0           0                     0           0
          0            0           0           0            0            0           0           0                     0            0           0            0           0                     0           0
          0            0           0           0            0            0           0           0                     0            0           0            0           0                     0           0
          0            0           0           0            0            0           0           0                     0            0           0            0           0                     0           0
          0            0           0           0            0            0           0           0                     0            0           0            0           0                     0           0
          0            0           0           0            0            0           0           0                     0            0           0            0           0                     0           0
          0            0           0           0            0            0           0           0                     0            0           0            0           0                     0           0
          0            0           0           0            0            0           0           0                     0            0           0            0           0                     0           0
          0            ?           0           ?            0            ?           0           ?                     0            ?           0            0           ?                     ?           ?

--- 9 row(s) selected.
>>
>>select bitand(a,b), bitand(c,d), bitand(e,f), bitand(g,h),
+>       bitand(i,i),
+>       bitand(j,k), bitand(l,m), bitand(n,o)
+>from t012t3;

(EXPR)       (EXPR)      (EXPR)       (EXPR)      (EXPR)                (EXPR)       (EXPR)       (EXPR)
-----------  ----------  -----------  ----------  --------------------  -----------  -----------  --------------------

          0           0            0           0                     0            0            0                     0
          1           1            1           1                     1            1            1                     1
          3           3            3           3                     3            3            3                     3
          4           4            4           4                     4            4            4                     4
         -1           1           -1           1                    -1            1            1                     1
         -3           3           -3           3                    -3            1            1                     1
         -4           4           -4           4                    -4            4            4                     4
          0           0            0           0                     0            0            0                     0
          ?           ?            ?           ?                     0            ?            ?                     ?

--- 9 row(s) selected.
>>
>>select bitor(a,b), bitor(c,d), bitor(e,f), bitor(g,h),
+>       bitor(i,i),
+>       bitor(j,k), bitor(l,m), bitor(n,o)
+>from t012t3;

(EXPR)       (EXPR)      (EXPR)       (EXPR)      (EXPR)                (EXPR)       (EXPR)       (EXPR)
-----------  ----------  -----------  ----------  --------------------  -----------  -----------  --------------------

          0           0            0           0                     0            0            0                     0
          1           1            1           1                     1            1            1                     1
          3           3            3           3                     3            3            3                     3
          4           4            4           4                     4            4            4                     4
         -1           1           -1           1                    -1           -1           -1                    -1
         -3           3           -3           3                    -3           -1           -1                    -1
         -4           4           -4           4                    -4           -4           -4                    -4
          1           1            1           1                     0            1            1                     1
          ?           ?            ?           ?                     0            ?            ?                     ?

--- 9 row(s) selected.
>>
>>select bitxor(a,b), bitxor(c,d), bitxor(e,f), bitxor(g,h),
+>       bitxor(i,i),
+>       bitxor(j,k), bitxor(l,m), bitxor(n,o)
+>from t012t3;

(EXPR)       (EXPR)      (EXPR)       (EXPR)      (EXPR)                (EXPR)       (EXPR)       (EXPR)
-----------  ----------  -----------  ----------  --------------------  -----------  -----------  --------------------

          0           0            0           0                     0            0            0                     0
          0           0            0           0                     0            0            0                     0
          0           0            0           0                     0            0            0                     0
          0           0            0           0                     0            0            0                     0
          0           0            0           0                     0           -2           -2                    -2
          0           0            0           0                     0           -2           -2                    -2
          0           0            0           0                     0           -8           -8                    -8
          1           1            1           1                     0            1            1                     1
          ?           ?            ?           ?                     0            ?            ?                     ?

--- 9 row(s) selected.
>>
>>select bitnot(a), bitnot(b), bitnot(c), bitnot(d), bitnot(e), bitnot(f),
+>       bitnot(g), bitnot(h), bitnot(i), bitnot(j), bitnot(k), bitnot(l),
+>       bitnot(m), bitnot(n), bitnot(o)
+>from t012t3;

(EXPR)  (EXPR)  (EXPR)  (EXPR)  (EXPR)       (EXPR)       (EXPR)      (EXPR)      (EXPR)                (EXPR)  (EXPR)  (EXPR)       (EXPR)      (EXPR)                (EXPR)
------  ------  ------  ------  -----------  -----------  ----------  ----------  --------------------  ------  ------  -----------  ----------  --------------------  ----------

    -1      -1   65535   65535           -1           -1  4294967295  4294967295                    -1      -1     255           -1  4294967295                    -1  4294967295
    -2      -2   65534   65534           -2           -2  4294967294  4294967294                    -2      -2     254           -2  4294967294                    -2  4294967294
    -4      -4   65532   65532           -4           -4  4294967292  4294967292                    -4      -4     252           -4  4294967292                    -4  4294967292
    -5      -5   65531   65531           -5           -5  4294967291  4294967291                    -5      -5     251           -5  4294967291                    -5  4294967291
     0       0   65534   65534            0            0  4294967294  4294967294                     0       0     254            0  4294967294                     0  4294967294
     2       2   65532   65532            2            2  4294967292  4294967292                     2       2     252            2  4294967292                     2  4294967292
     3       3   65531   65531            3            3  4294967291  4294967291                     3       3     251            3  4294967291                     3  4294967291
    -1      -2   65535   65534           -1           -2  4294967295  4294967294                    -1      -1     254           -1  4294967294                    -1  4294967294
    -1       ?   65535       ?           -1            ?  4294967295           ?                    -1       ?     255           -1           ?                     ?           ?

--- 9 row(s) selected.
>>
>>select bitand(a,c), bitand(a,d), bitand(a,e), bitand(a,f), bitand(a,g),
+>       bitand(a,h), bitand(a,i), bitand(a,j), bitand(a,k), bitand(a,l),
+>       bitand(a,m), bitand(a,n), bitand(a,o)
+>from t012t3;

(EXPR)       (EXPR)       (EXPR)       (EXPR)       (EXPR)                (EXPR)                (EXPR)                (EXPR)       (EXPR)       (EXPR)       (EXPR)       (EXPR)                (EXPR)
-----------  -----------  -----------  -----------  --------------------  --------------------  --------------------  -----------  -----------  -----------  -----------  --------------------  -----------

          0            0            0            0                     0                     0                     0            0            0            0            0                     0            0
          1            1            1            1                     1                     1                     1            1            1            1            1                     1            1
          3            3            3            3                     3                     3                     3            3            3            3            3                     3            3
          4            4            4            4                     4                     4                     4            4            4            4            4                     4            4
          1            1           -1           -1                     1                     1                    -1           -1            1           -1            1                    -1            1
          1            1           -3           -3                     1                     1                    -3           -3            1           -3            1                    -3            1
          4            4           -4           -4                     4                     4                    -4           -4            4           -4            4                    -4            4
          0            0            0            0                     0                     0                     0            0            0            0            0                     0            0
          0            ?            0            ?                     0                     ?                     0            ?            0            0            ?                     ?            ?

--- 9 row(s) selected.
>>
>>select bitor(a,c), bitor(a,d), bitor(a,e), bitor(a,f), bitor(a,g),
+>       bitor(a,h), bitor(a,i), bitor(a,j), bitor(a,k), bitor(a,l),
+>       bitor(a,m), bitor(a,n), bitor(a,o)
+>from t012t3;

(EXPR)       (EXPR)       (EXPR)       (EXPR)       (EXPR)                (EXPR)                (EXPR)                (EXPR)       (EXPR)       (EXPR)       (EXPR)       (EXPR)                (EXPR)
-----------  -----------  -----------  -----------  --------------------  --------------------  --------------------  -----------  -----------  -----------  -----------  --------------------  -----------

          0            0            0            0                     0                     0                     0            0            0            0            0                     0            0
          1            1            1            1                     1                     1                     1            1            1            1            1                     1            1
          3            3            3            3                     3                     3                     3            3            3            3            3                     3            3
          4            4            4            4                     4                     4                     4            4            4            4            4                     4            4
         -1           -1           -1           -1                    -1                    -1                    -1           -1           -1           -1           -1                    -1           -1
         -1           -1           -3           -3                    -1                    -1                    -3           -3           -1           -3           -1                    -3           -1
         -4           -4           -4           -4                    -4                    -4                    -4           -4           -4           -4           -4                    -4           -4
          0            1            0            1                     0                     1                     0            0            1            0            1                     0            1
          0            ?            0            ?                     0                     ?                     0            ?            0            0            ?                     ?            ?

--- 9 row(s) selected.
>>
>>select bitxor(a,c), bitxor(a,d), bitxor(a,e), bitxor(a,f), bitxor(a,g),
+>       bitxor(a,h), bitxor(a,i), bitxor(a,j), bitxor(a,k), bitxor(a,l),
+>       bitxor(a,m), bitxor(a,n), bitxor(a,o)
+>from t012t3;

(EXPR)       (EXPR)       (EXPR)       (EXPR)       (EXPR)                (EXPR)                (EXPR)                (EXPR)       (EXPR)       (EXPR)       (EXPR)       (EXPR)                (EXPR)
-----------  -----------  -----------  -----------  --------------------  --------------------  --------------------  -----------  -----------  -----------  -----------  --------------------  -----------

          0            0            0            0                     0                     0                     0            0            0            0            0                     0            0
          0            0            0            0                     0                     0                     0            0            0            0            0                     0            0
          0            0            0            0                     0                     0                     0            0            0            0            0                     0            0
          0            0            0            0                     0                     0                     0            0            0            0            0                     0            0
         -2           -2            0            0                    -2                    -2                     0            0           -2            0           -2                     0           -2
         -2           -2            0            0                    -2                    -2                     0            0           -2            0           -2                     0           -2
         -8           -8            0            0                    -8                    -8                     0            0           -8            0           -8                     0           -8
          0            1            0            1                     0                     1                     0            0            1            0            1                     0            1
          0            ?            0            ?                     0                     ?                     0            ?            0            0            ?                     ?            ?

--- 9 row(s) selected.
>>
>>select converttobits(a), converttobits(b), converttobits(c), converttobits(d),
+>       converttobits(e), converttobits(f), converttobits(g), converttobits(h),
+>       converttobits(i), converttobits(j), converttobits(k), converttobits(l),
+>       converttobits(m), converttobits(n), converttobits(o)
+>from t012t3;

(EXPR)            (EXPR)            (EXPR)            (EXPR)            (EXPR)                            (EXPR)                            (EXPR)                            (EXPR)                            (EXPR)                                                            (EXPR)                    (EXPR)            (EXPR)                                                    (EXPR)                                                                    (EXPR)                                                                                                                                    (EXPR)
----------------  ----------------  ----------------  ----------------  --------------------------------  --------------------------------  --------------------------------  --------------------------------  ----------------------------------------------------------------  ------------------------  ----------------  --------------------------------------------------------  ------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------  ------------------------------------------------------------------------

0000000000000000  0000000000000000  0000000000000000  0000000000000000  00000000000000000000000000000000  00000000000000000000000000000000  00000000000000000000000000000000  00000000000000000000000000000000  0000000000000000000000000000000000000000000000000000000000000000  001100000011000000110000  0011000000110000  00110000001100000011000000110000001100000011000000110000  001100000011000000110000001100000011000000110000001100000011000000110000  0011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110000  001100000011000000110000001100000011000000110000001100000011000000110000
0000000100000000  0000000100000000  0000000100000000  0000000100000000  00000001000000000000000000000000  00000001000000000000000000000000  00000001000000000000000000000000  00000001000000000000000000000000  0000000100000000000000000000000000000000000000000000000000000000  001100000011000000110001  0011000000110001  00110000001100000011000000110000001100000011000000110001  001100000011000000110000001100000011000000110000001100000011000000110001  0011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110001  001100000011000000110000001100000011000000110000001100000011000000110001
0000001100000000  0000001100000000  0000001100000000  0000001100000000  00000011000000000000000000000000  00000011000000000000000000000000  00000011000000000000000000000000  00000011000000000000000000000000  0000001100000000000000000000000000000000000000000000000000000000  001100000011000000110011  0011000000110011  00110000001100000011000000110000001100000011000000110011  001100000011000000110000001100000011000000110000001100000011000000110011  0011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110011  001100000011000000110000001100000011000000110000001100000011000000110011
0000010000000000  0000010000000000  0000010000000000  0000010000000000  00000100000000000000000000000000  00000100000000000000000000000000  00000100000000000000000000000000  00000100000000000000000000000000  0000010000000000000000000000000000000000000000000000000000000000  001100000011000000110100  0011000000110100  00110000001100000011000000110000001100000011000000110100  001100000011000000110000001100000011000000110000001100000011000000110100  0011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110100  001100000011000000110000001100000011000000110000001100000011000000110100
1111111111111111  1111111111111111  0000000100000000  0000000100000000  11111111111111111111111111111111  11111111111111111111111111111111  00000001000000000000000000000000  00000001000000000000000000000000  1111111111111111111111111111111111111111111111111111111111111111  101100000011000000110001  0011000000110001  10110000001100000011000000110000001100000011000000110001  001100000011000000110000001100000011000000110000001100000011000000110001  1011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110001  001100000011000000110000001100000011000000110000001100000011000000110001
1111110111111111  1111110111111111  0000001100000000  0000001100000000  11111101111111111111111111111111  11111101111111111111111111111111  00000011000000000000000000000000  00000011000000000000000000000000  1111110111111111111111111111111111111111111111111111111111111111  101100000011000000110011  0011000000110011  10110000001100000011000000110000001100000011000000110011  001100000011000000110000001100000011000000110000001100000011000000110011  1011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110011  001100000011000000110000001100000011000000110000001100000011000000110011
1111110011111111  1111110011111111  0000010000000000  0000010000000000  11111100111111111111111111111111  11111100111111111111111111111111  00000100000000000000000000000000  00000100000000000000000000000000  1111110011111111111111111111111111111111111111111111111111111111  101100000011000000110100  0011000000110100  10110000001100000011000000110000001100000011000000110100  001100000011000000110000001100000011000000110000001100000011000000110100  1011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110100  001100000011000000110000001100000011000000110000001100000011000000110100
0000000000000000  0000000100000000  0000000000000000  0000000100000000  00000000000000000000000000000000  00000001000000000000000000000000  00000000000000000000000000000000  00000001000000000000000000000000  0000000000000000000000000000000000000000000000000000000000000000  001100000011000000110000  0011000000110001  00110000001100000011000000110000001100000011000000110000  001100000011000000110000001100000011000000110000001100000011000000110001  0011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110000001100000011000000110000  001100000011000000110000001100000011000000110000001100000011000000110001
0000000000000000  ?                 0000000000000000  ?                 00000000000000000000000000000000  ?                                 00000000000000000000000000000000  ?                                 0000000000000000000000000000000000000000000000000000000000000000  ?                         0011000000110000  00110000001100000011000000110000001100000011000000110000  ?                                                                         ?                                                                                                                                         ?                                                                       

--- 9 row(s) selected.
>>
>>select bitextract(cast(3 as smallint), 15, 1) from (values(1)) x(a);

(EXPR)     
-----------

          1

--- 1 row(s) selected.
>>select bitextract(cast(3 as smallint), 14, 1) from (values(1)) x(a);

(EXPR)     
-----------

          1

--- 1 row(s) selected.
>>select bitextract(cast(3 as smallint), 14, 2) from (values(1)) x(a);

(EXPR)     
-----------

          3

--- 1 row(s) selected.
>>select bitextract(cast(3 as smallint), 0, 1) from (values(1)) x(a);

(EXPR)     
-----------

          0

--- 1 row(s) selected.
>>select bitextract(cast(3 as smallint), 0, 15) from (values(1)) x(a);

(EXPR)     
-----------

          1

--- 1 row(s) selected.
>>select bitextract(cast(3 as smallint), 0, 16) from (values(1)) x(a);

(EXPR)     
-----------

          3

--- 1 row(s) selected.
>>select bitextract(3, 0, 8) from (values(1)) x(a);

(EXPR)     
-----------

          3

--- 1 row(s) selected.
>>select bitextract(3, 7, 1) from (values(1)) x(a);

(EXPR)     
-----------

          1

--- 1 row(s) selected.
>>select bitextract(3, 8, 1) from (values(1)) x(a);

(EXPR)     
-----------

          0

--- 1 row(s) selected.
>>
>>select a from t012t3 where a = bitand(a,a);

A     
------

     0
     1
     3
     4
    -1
    -3
    -4
     0
     0

--- 9 row(s) selected.
>>select b from t012t3 where b = bitor(b,b);

B     
------

     0
     1
     3
     4
    -1
    -3
    -4
     1

--- 8 row(s) selected.
>>select c from t012t3 where c = bitxor(c,c);

C    
-----

    0
    0
    0

--- 3 row(s) selected.
>>
>>select bitand(a,a) + bitor(a,a) + bitxor(a,a) from t012t3;

(EXPR)              
--------------------

                   0
                   2
                   6
                   8
                  -2
                  -6
                  -8
                   0
                   0

--- 9 row(s) selected.
>>select converttobits(bitand(a,a) + bitor(a,a) + bitxor(a,a)) from t012t3;

(EXPR)                                                          
----------------------------------------------------------------

0000000000000000000000000000000000000000000000000000000000000000
0000001000000000000000000000000000000000000000000000000000000000
0000011000000000000000000000000000000000000000000000000000000000
0000100000000000000000000000000000000000000000000000000000000000
1111111011111111111111111111111111111111111111111111111111111111
1111101011111111111111111111111111111111111111111111111111111111
1111100011111111111111111111111111111111111111111111111111111111
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000

--- 9 row(s) selected.
>>select converttobits(bitnot(bitand(a,a) + bitor(a,a) + bitxor(a,a))) from t012t3;

(EXPR)                                                          
----------------------------------------------------------------

1111111111111111111111111111111111111111111111111111111111111111
1111110111111111111111111111111111111111111111111111111111111111
1111100111111111111111111111111111111111111111111111111111111111
1111011111111111111111111111111111111111111111111111111111111111
0000000100000000000000000000000000000000000000000000000000000000
0000010100000000000000000000000000000000000000000000000000000000
0000011100000000000000000000000000000000000000000000000000000000
1111111111111111111111111111111111111111111111111111111111111111
1111111111111111111111111111111111111111111111111111111111111111

--- 9 row(s) selected.
>>
>>-- various operations involving number datatypes and string functions.
>>select '1 ' || 12.34e3 from dual;

(EXPR)                     
---------------------------

1 1.23400000000000000E+004 

--- 1 row(s) selected.
>>select 12 || '1 ' || 12.34e3 from dual;

(EXPR)                         
-------------------------------

121 1.23400000000000000E+004   

--- 1 row(s) selected.
>>select concat(_ucs2'2233', 12.34e3) from dual;

(EXPR)                                                    
----------------------------------------------------------

22331.23400000000000000E+004                              

--- 1 row(s) selected.
>>select case when 1 = 1 then 12.3e34 else '1' end from dual;

(EXPR)                   
-------------------------

1.23000000000000000E+035 

--- 1 row(s) selected.
>>
>>drop table if exists t012t4;

--- SQL operation complete.
>>
>>create table t012t4 (
+>c1 float,
+>c2 numeric,
+>c3 numeric(128),
+>c4 numeric(10,5),
+>c5 decimal,
+>c6 decimal(18),
+>c7 decimal(10,5)
+>);

--- SQL operation complete.
>>insert into t012t4 values (1.23, 1.23, 1.23, 1.23, 1.23, 1.23, 1.23);

--- 1 row(s) inserted.
>>select * from t012t4;

C1                         C2           C3                                                                                                                                 C4                     C5          C6                   C7
-------------------------  -----------  ---------------------------------------------------------------------------------------------------------------------------------  ---------------------  ----------  -------------------  ------------

 1.23000000000000000E+000            1                                                                                                                                  1                1.23000           1                    1       1.23000

--- 1 row(s) selected.
>>
>>select CONCAT(c1,'ZZZ') from t012t4;

(EXPR)                      
----------------------------

1.23000000000000000E+000ZZZ 

--- 1 row(s) selected.
>>select CONCAT(c2,'ZZZ') from t012t4;

(EXPR)        
--------------

1ZZZ          

--- 1 row(s) selected.
>>select CONCAT(c3,'ZZZ') from t012t4;

(EXPR)
------------------------------------------------------------------------------------------------------------------------------------

1ZZZ                                                                                                                                

--- 1 row(s) selected.
>>select CONCAT(c4,'ZZZ') from t012t4;

(EXPR)                  
------------------------

1.23000ZZZ              

--- 1 row(s) selected.
>>select CONCAT(c5,'ZZZ') from t012t4;

(EXPR)       
-------------

1ZZZ         

--- 1 row(s) selected.
>>select CONCAT(c6,'ZZZ') from t012t4;

(EXPR)                
----------------------

1ZZZ                  

--- 1 row(s) selected.
>>select CONCAT(c7,'ZZZ') from t012t4;

(EXPR)         
---------------

1.23000ZZZ     

--- 1 row(s) selected.
>>
>>select LPAD(c1,2,'ZZZ') from t012t4;

(EXPR)
------

1.    

--- 1 row(s) selected.
>>select LPAD(c2,2,'ZZZ') from t012t4;

(EXPR)
------

Z1    

--- 1 row(s) selected.
>>select LPAD(c3,2,'ZZZ') from t012t4;

(EXPR)
------

Z1    

--- 1 row(s) selected.
>>select LPAD(c4,2,'ZZZ') from t012t4;

(EXPR)
------

1.    

--- 1 row(s) selected.
>>select LPAD(c5,2,'ZZZ') from t012t4;

(EXPR)
------

Z1    

--- 1 row(s) selected.
>>select LPAD(c6,2,'ZZZ') from t012t4;

(EXPR)
------

Z1    

--- 1 row(s) selected.
>>select LPAD(c7,2,'ZZZ') from t012t4;

(EXPR)
------

1.    

--- 1 row(s) selected.
>>
>>select LTRIM(c1) from t012t4;

(EXPR)                   
-------------------------

1.23000000000000000E+000 

--- 1 row(s) selected.
>>select LTRIM(c2) from t012t4;

(EXPR)     
-----------

1          

--- 1 row(s) selected.
>>select LTRIM(c3) from t012t4;

(EXPR)
---------------------------------------------------------------------------------------------------------------------------------

1                                                                                                                                

--- 1 row(s) selected.
>>select LTRIM(c4) from t012t4;

(EXPR)               
---------------------

1.23000              

--- 1 row(s) selected.
>>select LTRIM(c5) from t012t4;

(EXPR)    
----------

1         

--- 1 row(s) selected.
>>select LTRIM(c6) from t012t4;

(EXPR)             
-------------------

1                  

--- 1 row(s) selected.
>>select LTRIM(c7) from t012t4;

(EXPR)      
------------

1.23000     

--- 1 row(s) selected.
>>
>>select OCTET_LENGTH(c1) from t012t4;

(EXPR)    
----------

        24

--- 1 row(s) selected.
>>select OCTET_LENGTH(c2) from t012t4;

(EXPR)    
----------

         1

--- 1 row(s) selected.
>>select OCTET_LENGTH(c3) from t012t4;

(EXPR)    
----------

         1

--- 1 row(s) selected.
>>select OCTET_LENGTH(c4) from t012t4;

(EXPR)    
----------

         7

--- 1 row(s) selected.
>>select OCTET_LENGTH(c5) from t012t4;

(EXPR)    
----------

         1

--- 1 row(s) selected.
>>select OCTET_LENGTH(c6) from t012t4;

(EXPR)    
----------

         1

--- 1 row(s) selected.
>>select OCTET_LENGTH(c7) from t012t4;

(EXPR)    
----------

         7

--- 1 row(s) selected.
>>
>>select RPAD(c1,2,'ZZZ') from t012t4;

(EXPR)
------

1.    

--- 1 row(s) selected.
>>select RPAD(c2,2,'ZZZ') from t012t4;

(EXPR)
------

1Z    

--- 1 row(s) selected.
>>select RPAD(c3,2,'ZZZ') from t012t4;

(EXPR)
------

1Z    

--- 1 row(s) selected.
>>select RPAD(c4,2,'ZZZ') from t012t4;

(EXPR)
------

1.    

--- 1 row(s) selected.
>>select RPAD(c5,2,'ZZZ') from t012t4;

(EXPR)
------

1Z    

--- 1 row(s) selected.
>>select RPAD(c6,2,'ZZZ') from t012t4;

(EXPR)
------

1Z    

--- 1 row(s) selected.
>>select RPAD(c7,2,'ZZZ') from t012t4;

(EXPR)
------

1.    

--- 1 row(s) selected.
>>
>>select RTRIM(c1) from t012t4;

(EXPR)                   
-------------------------

1.23000000000000000E+000 

--- 1 row(s) selected.
>>select RTRIM(c2) from t012t4;

(EXPR)     
-----------

1          

--- 1 row(s) selected.
>>select RTRIM(c3) from t012t4;

(EXPR)
---------------------------------------------------------------------------------------------------------------------------------

1                                                                                                                                

--- 1 row(s) selected.
>>select RTRIM(c4) from t012t4;

(EXPR)               
---------------------

1.23000              

--- 1 row(s) selected.
>>select RTRIM(c5) from t012t4;

(EXPR)    
----------

1         

--- 1 row(s) selected.
>>select RTRIM(c6) from t012t4;

(EXPR)             
-------------------

1                  

--- 1 row(s) selected.
>>select RTRIM(c7) from t012t4;

(EXPR)      
------------

1.23000     

--- 1 row(s) selected.
>>
>>select TRIM(c1) from t012t4;

(EXPR)                   
-------------------------

1.23000000000000000E+000 

--- 1 row(s) selected.
>>select TRIM(c2) from t012t4;

(EXPR)     
-----------

1          

--- 1 row(s) selected.
>>select TRIM(c3) from t012t4;

(EXPR)
---------------------------------------------------------------------------------------------------------------------------------

1                                                                                                                                

--- 1 row(s) selected.
>>select TRIM(c4) from t012t4;

(EXPR)               
---------------------

1.23000              

--- 1 row(s) selected.
>>select TRIM(c5) from t012t4;

(EXPR)    
----------

1         

--- 1 row(s) selected.
>>select TRIM(c6) from t012t4;

(EXPR)             
-------------------

1                  

--- 1 row(s) selected.
>>select TRIM(c7) from t012t4;

(EXPR)      
------------

1.23000     

--- 1 row(s) selected.
>>
>>-- negative BIT function tests
>>select bitand(1, 1.0) from (values(1)) x(a);

*** ERROR[4047] The operands of function BITAND must have a scale of 0.

*** ERROR[8822] The statement was not prepared.

>>select bitor(1, 1.0) from (values(1)) x(a);

*** ERROR[4047] The operands of function BITOR must have a scale of 0.

*** ERROR[8822] The statement was not prepared.

>>select bitxor(1, 1.0) from (values(1)) x(a);

*** ERROR[4047] The operands of function BITXOR must have a scale of 0.

*** ERROR[8822] The statement was not prepared.

>>select bitand(1e0, 1) from (values(1)) x(a);

*** ERROR[4046] The operands of function BITAND must be exact numeric.

*** ERROR[8822] The statement was not prepared.

>>select bitor('a', 1) from (values(1)) x(a);

*** ERROR[4059] The first operand of function BITOR must be numeric.

*** ERROR[8822] The statement was not prepared.

>>select bitnot(1,1) from (values(1)) x(a);

*** ERROR[15001] A syntax error occurred at or before: 
select bitnot(1,1) from (values(1)) x(a);
               ^ (16 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>
>>-- test ASCII, CHAR functions
>>-- all tests moved to TEST038(Core)
>>-- * select b, { fn CHAR(ASCII(b)) }, c, { fn CHAR(ASCII(c)) } from t012t1;
>>-- * select b, CHAR(ASCII(b)), c, CHAR(ASCII(c)) from t012t1;
>>-- * select 'c', CHAR(ASCII('cba')) from t012t1;
>>
>>-- * select 65, { fn ASCII(CHAR(65)) } from t012t1;
>>-- * select 65, ASCII(CHAR(65)) from t012t1;
>>
>>-- -ve
>>-- all tests moved to TEST038(Core)
>>-- * select { fn ASCII(1) } from t012t1;
>>
>>-- * select CHAR(b) from t012t1;
>>-- * select CHAR(256) from t012t1;
>>-- * select CHAR(1.1) from t012t1;
>>
>>
>>-- INSERT function
>>-- test moved to TEST038(Core)
>>-- * select insert('abc', 2, 1, 'zz') from t012t1;
>>
>>-- REPEAT
>>-- all tests moved to TEST038(Core)
>>-- * select repeat('aa', 10) from t012t1;
>>
>>-- test fix to genesis case: 10-981211-6071
>>-- makes sure we catch and diagnose invalid repeat count
>>-- * select REPEAT(b, 9999999999999999999) from t012t1;
>>-- * select REPEAT(c, 9999999999999999999) from t012t1;
>>
>>-- REPLACE
>>-- test moved to TEST038(Core)
>>-- * select replace('abcdabcdab', 'cd', 'abc') from t012t1;
>>
>>-- SPACE
>>-- test moved to TEST038(Core)
>>-- * select 's' || space(10) || 'e', char_length(space(10)) from t012t1;
>>
>>-- VEG conversion should not change the result of string function.
>>-- should return 10
>>-- test moved to TEST038(Core)
>>-- * select char_length(b) from t012t1 where b = 'abcdef';
>>
>>-- should return 'abcdef    abcdef    '
>>-- test moved to TEST038(Core)
>>-- * select repeat(b, 2) from t012t1 where b = 'abcdef';
>>
>>
>>?section clnup
>>-- CLEANUP database;
>>drop table t012t1;

--- SQL operation complete.
>>?ifMX
>>drop table t012ut1;

--- SQL operation complete.
>>?ifMX
>>
>>drop table t012t3;

--- SQL operation complete.
>>drop table t012t4;

--- SQL operation complete.
>>
>>log;