>>
>>create table tab_iso91 (c char(10) character set ISO88591);

--- SQL operation complete.
>>create table tab_ucs2 (d char(10) character set UCS2);

--- SQL operation complete.
>>create table tab_int( i int);

--- SQL operation complete.
>>
>>?section dml
>>
>>--positive
>>
>>insert into tab_int values(ASCII('asdf'));

--- 1 row(s) inserted.
>>insert into tab_int values(ASCII(x'00'));

--- 1 row(s) inserted.
>>insert into tab_int values(code_value('zxcv'));

--- 1 row(s) inserted.
>>insert into tab_int values(code_value('zxcv'));

--- 1 row(s) inserted.
>>insert into tab_int values(code_value(_ucs2 x'1234 5678'));

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

I          
-----------

         97
          0
        122
        122
       4660

--- 5 row(s) selected.
>>
>>insert into tab_iso91 values(CHAR(43));

--- 1 row(s) inserted.
>>insert into tab_iso91 values(CHAR(65, ISO88591));

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

C         
----------

+         
A         

--- 2 row(s) selected.
>>
>>insert into tab_ucs2 values(CHAR(1041, UCS2));

--- 1 row(s) inserted.
>> -- cyrillic capital letter BE
>>insert into tab_ucs2 values(CHAR(9617, UCS2));

--- 1 row(s) inserted.
>> -- light shade symbol
>>select code_value(d) from tab_ucs2;

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

      1041
      9617

--- 2 row(s) selected.
>>
>>select UPPER(_UCS2'ALL IN CAPS') from (values(1))x;

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

ALL IN CAPS                                                       

--- 1 row(s) selected.
>>select UPPER(_UCS2'all in small case') from (values(1))x;

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

ALL IN SMALL CASE                                                                                     

--- 1 row(s) selected.
>>select lower(_UCS2'ALL IN CAPS') from (values(1))x;

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

all in caps           

--- 1 row(s) selected.
>>select lower(_UCS2'all in small case') from (values(1))x;

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

all in small case                 

--- 1 row(s) selected.
>>
>>-- title cases.
>>--0x01F1 "DZ" capital dz is U+01F2 "Dz" capital d with small z. 
>>
>>
>>-- should get 0x01f1 = 497
>>select code_value(UPPER(_UCS2 x'01f2')) from (values(1))x;

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

       497

--- 1 row(s) selected.
>>
>>-- should get 0x01f3 = 499
>>select code_value(lower(_UCS2 x'01f2')) from (values(1))x;

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

       499

--- 1 row(s) selected.
>>
>>--00e0 and 00f6
>>select converttohex(upper(_ucs2 x'00e0 00f6')) from (values(1))x;

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

00C000D6                

--- 1 row(s) selected.
>>select converttohex(lower(_ucs2 x'00c0 00d6')) from (values(1))x;

(EXPR)  
--------

00E000F6

--- 1 row(s) selected.
>>select converttohex(code_value(_ucs2 x'00c0 00d6')) from (values(1))x;

(EXPR)  
--------

C0000000

--- 1 row(s) selected.
>>
>>--0130
>>select converttohex(lower(_UCS2 x'0130')) from (values(1))x;

(EXPR)
------

0069  

--- 1 row(s) selected.
>>select converttohex(upper(_UCS2 x'0130')) from (values(1))x;

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

0130        

--- 1 row(s) selected.
>>
>>--0131
>>select converttohex(lower(_UCS2 x'0131')) from (values(1))x;

(EXPR)
------

0131  

--- 1 row(s) selected.
>>select converttohex(upper(_UCS2 x'0131')) from (values(1))x;

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

0049        

--- 1 row(s) selected.
>>
>>-- 1e9a -> 0041 02be
>>-- 00df -> 0053 0053
>>select char_length(upper(_UCS2 X'1e9a 00DF')) from (values(1))x;

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

         4

--- 1 row(s) selected.
>>select upper(repeat(_UCS2 X'00DF', 10)) from (values(1))x;

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

SSSSSSSSSSSSSSSSSSSS                                        

--- 1 row(s) selected.
>>
>>--both have the same upper/lower case
>>select code_value(upper(_ucs2 x'0000')) from (values(1))x;

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

         0

--- 1 row(s) selected.
>>select code_value(lower(_ucs2 x'FFFD')) from (values(1))x;

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

     65533

--- 1 row(s) selected.
>>
>>--l/r trims
>>select char_length(ltrim(_ucs2'  abc  ')) from (values(1))x;

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

         5

--- 1 row(s) selected.
>>select rtrim(_ucs2'  abc  ') from (values(1))x;

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

  abc         

--- 1 row(s) selected.
>>
>>--space
>>select * from (values(1))x where char_length(space(30, iso88591)) = 30;

(EXPR)
------

     1

--- 1 row(s) selected.
>>select * from (values(1))x where char_length(space(30, ucs2)) = 30;

(EXPR)
------

     1

--- 1 row(s) selected.
>>select space(10) from (values(1))x;

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

          

--- 1 row(s) selected.
>>select space(10, ucs2) from (values(1))x;

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

                    

--- 1 row(s) selected.
>>
>>--replace
>>select replace(_ucs2'1abcdeab', _ucs2'abcdeab', _ucs2 x'0058') from (values(1))x;

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

1X              

--- 1 row(s) selected.
>>select replace(_ucs2'1abcdeab', _ucs2'abcdeab', _ucs2 x'') from (values(1))x;

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

1               

--- 1 row(s) selected.
>>select replace(_ucs2'abc', _ucs2'z', _ucs2 x'') from (values(1))x;

(EXPR)
------

abc   

--- 1 row(s) selected.
>>
>>--UCS2 column can be used in where clause
>>select d from tab_ucs2 where d = _ucs2'abc';

--- 0 row(s) selected.
>>
>>--negative
>>
>>--charset mismatch
>>select ascii(_ucs2'asdf') from (values(1)) x;

*** ERROR[4106] The character set for the operand of function ASCII must be ISO88591.

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

>>insert into tab_ucs2 values(CHAR(43));

--- 1 row(s) inserted.
>>
>>--illegal chars
>>select char(-1) from (values(1)) x;

*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:-1 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S16S64 Operation:RANGE_LOW.

--- 0 row(s) selected.
>>select char(257) from (values(1)) x;

*** ERROR[8428] The argument to function CHAR is not valid.

--- 0 row(s) selected.
>>select char(-10, ucs2) from (values(1)) x;

*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:-10 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S16S64 Operation:RANGE_LOW.

--- 0 row(s) selected.
>>select char(65535, ucs2) from (values(1)) x;

*** ERROR[8428] The argument to function CHAR is not valid.

--- 0 row(s) selected.
>>
>>-- overflow 
>>
>>--ok as 4096 bytes or 2048 UCS2 chars is the upper limit. 682x 3 = 2046 
>>select char_length(upper(repeat(_ucs2 x'0390', 682))) from (values(1))x;

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

      2046

--- 1 row(s) selected.
>>
>>--should see an overflow error (683x3=2049)
>>select char_length(upper(repeat(_ucs2 x'0390', 683))) from (values(1))x;

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

      2049

--- 1 row(s) selected.
>>
>>--disabled old func names
>>select unicode_value(_ucs2 x'0390', ucs2) from (values(1))x;

*** ERROR[4450] Function TRAFODION."_UDF_".UNICODE_VALUE is not a built-in function or registered user-defined function.

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

>>select unicode_char(0390, ucs2) from (values(1))x;

*** ERROR[4450] Function TRAFODION."_UDF_".UNICODE_CHAR is not a built-in function or registered user-defined function.

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

>>
>>--replace(), zero length pattern
>>select replace(_ucs2'abc', _ucs2'', _ucs2 x'') from (values(1))x;

*** ERROR[8428] The argument to function REPLACE is not valid.

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

>>
>>-- ANSI char matching rules are preserved (i.e., relaxation only done on UCS2 hostvars)
>>select _ucs2'abc' || '' from (values(1))x;

(EXPR)
------

abc   

--- 1 row(s) selected.
>>
>>select left(repeat(_ucs2 x'00c0 00d6', 25000), 10) from (values(1))x;

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

          

--- 1 row(s) selected.
>>select _iso88591 x'$#' from (values(1))x;

*** ERROR[3402] The format of the ISO88591 hexadecimal string literal is not valid.

*** ERROR[15001] A syntax error occurred at or before: 
select _iso88591 x'$#' from (values(1))x;
                     ^ (22 characters from start of SQL statement)

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

>>select _ucs2 x'0a$#' from (values(1))x;

*** ERROR[3402] The format of the UCS2 hexadecimal string literal is not valid.

*** ERROR[15001] A syntax error occurred at or before: 
select _ucs2 x'0a$#' from (values(1))x;
                   ^ (20 characters from start of SQL statement)

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

>>
>>--large length values should not cause core-dump (case 10-040218-2346)
>>select space(2147483647,ucs2) from (values (1)) as x;

*** ERROR[4129] The character-typed result of the function REPEAT is longer than the maximum supported size.

*** ERROR[4062] The preceding error actually occurred in function SPACE.

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

>>select rpad(N'test',2147483647,N'T') from (values(1)) as x;

*** ERROR[4129] The character-typed result of the function RPAD is longer than the maximum supported size.

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

>>select lpad(N'test',2147483647,N'T') from (values(1)) as x;

*** ERROR[4129] The character-typed result of the function REPEAT is longer than the maximum supported size.

*** ERROR[4062] The preceding error actually occurred in function LPAD.

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

>>select substring(_ucs2'character set test' from 1 for 2147483647) from (values(1)) as x;

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

character set test                  

--- 1 row(s) selected.
>>
>>log;
