DB2 Version 9.7 for Linux, UNIX, and Windows

RIGHT scalar function


>>-RIGHT--(--string-expression--,--length--+--------------------+--)-><
                                           '-,--+-CODEUNITS16-+-'      
                                                +-CODEUNITS32-+        
                                                '-OCTETS------'

The schema is SYSIBM. The SYSFUN version of the RIGHT function continues to be available.

The RIGHT function returns the rightmost string of string-expression of length length, expressed in the specified string unit. If string-expression is a character string, the result is a character string. If string-expression is a graphic string, the result is a graphic string

string-expression

An expression that specifies the string from which the result is derived. The expression must return a value that is a built-in string, numeric, or datetime data type. If the value is not a string data type, it is implicitly cast to VARCHAR before evaluating the function. A substring of string-expression is zero or more contiguous code points of string-expression.

length

An expression that specifies the length of the result. The expression must return a value that is a built-in numeric, CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function. The value must be between 0 and the length of string-expression, expressed in units that are either implicitly or explicitly specified (SQLSTATE 22011) with one exception. If the value is specified as a constant without explicitly specifying the string unit, the value can exceed the length attribute of string-expression in the implicit string unit. If OCTETS is specified and the result is graphic data, the value must be an even number between 0 and twice the length attribute of string-expression (SQLSTATE 428GC).

CODEUNITS16, CODEUNITS32, or OCTETS

Specifies the string unit of length.

CODEUNITS16 specifies that length is expressed in 16-bit UTF-16 code units. CODEUNITS32 specifies that length is expressed in 32-bit UTF-32 code units. OCTETS specifies that length is expressed in bytes.

If the string unit is specified as CODEUNITS16 or CODEUNITS32, and string-expression is a binary string or bit data, an error is returned (SQLSTATE 428GC). If the string unit is specified as OCTETS and string-expression is a graphic string, length must be an even number; otherwise, an error is returned (SQLSTATE 428GC). If a string unit is not explicitly specified, the data type of the result determines the unit that is used. If the result is graphic data, length is expressed in two-byte units; otherwise, it is expressed in bytes. For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see "String units in built-in functions" in "Character strings".

The string-expression is padded on the right with the necessary number of padding characters so that the specified substring of string-expression always exists. The character used for padding is the same character that is used to pad the string in contexts where padding would occur. For more information on padding, see "String assignments" in "Assignments and comparisons".

The result of the function is a varying-length string with a length attribute that depends on how length and the string unit are specified. If length is not specified using a constant or the string unit is explicitly specified, then the length attribute is the same as the length attribute of string-expression. If length is specified using a constant and the string unit is not specified, then the length attribute is the maximum of length and the length attribute of string-expression. The data type of the result depends on the data type of string-expression:

VARCHAR if string-expression is CHAR or VARCHAR
CLOB if string-expression is CLOB
VARGRAPHIC if string-expression is GRAPHIC or VARGRAPHIC
DBCLOB if string-expression is DBCLOB
BLOB if string-expression is BLOB

The actual length of the result (in string units) is length.

If any argument can be null, the result can be null; if any argument is null, the result is the null value.

Examples:

Assume that variable ALPHA has a value of 'ABCDEF'. The following statement:
```
   SELECT RIGHT(ALPHA,3)
     FROM SYSIBM.SYSDUMMY1
```
returns 'DEF', which are the three rightmost characters in ALPHA.
Assume that variable NAME, which is defined as VARCHAR(50), has a value of 'KATIE AUSTIN', and that the integer variable LASTNAME_LEN has a value of 6. The following statement:
```
   SELECT RIGHT(NAME,LASTNAME_LEN)
     FROM SYSIBM.SYSDUMMY1
```
returns the value 'AUSTIN'.

The following statement returns a zero-length string.

   SELECT RIGHT('ABCABC',0)
     FROM SYSIBM.SYSDUMMY1

The FIRSTNME column in the EMPLOYEE table is defined as VARCHAR(12). Find the first name of an employee whose last name is 'BROWN' and return the first name in a 10-byte string.
```
   SELECT RIGHT(FIRSTNME, 10)
     FROM EMPLOYEE
     WHERE LASTNAME = 'BROWN'
```
returns a VARCHAR(12) string that has the value 'DAVID' followed by five blank characters.

In a Unicode database, FIRSTNAME is a VARCHAR(12) column. One of its values is the 6-character string 'Jürgen'. When FIRSTNAME has this value:

   Function...                       Returns...

   RIGHT(FIRSTNAME,5,CODEUNITS32)  'ürgen' -- x'C3BC7267656E'
   RIGHT(FIRSTNAME,5,CODEUNITS16)  'ürgen' -- x'C3BC7267656E'
   RIGHT(FIRSTNAME,5,OCTETS)       'rgen'  -- x'207267656E', a truncated string

The following example works with the Unicode string '&N~AB', where '&' is the musical symbol G clef character, and '~' is the combining tilde character. This string is shown below in different Unicode encoding forms:
'&' 'N' '~' 'A' 'B'

UTF-8 X'F09D849E' X'4E' X'CC83' X'41' X'42'

UTF-16BE X'D834DD1E' X'004E' X'0303' X'0041' X'0042'
Assume that the variable UTF8_VAR, with a length attribute of 20 bytes, contains the UTF-8 representation of the string.
```
   SELECT RIGHT(UTF8_VAR, 2, CODEUNITS16),
     RIGHT(UTF8_VAR, 2, CODEUNITS32),
     RIGHT(UTF8_VAR, 2, OCTETS)
   FROM SYSIBM.SYSDUMMY1
```
returns the values 'AB', 'AB', and 'AB', respectively.
```
   SELECT RIGHT(UTF8_VAR, 5, CODEUNITS16),
     RIGHT(UTF8_VAR, 5, CODEUNITS32),
     RIGHT(UTF8_VAR, 5, OCTETS)
   FROM SYSIBM.SYSDUMMY1
```
returns the values '?N~AB', '&N~AB', and 'N~AB', respectively, where '?' is X'EDB49E'.
```
   SELECT RIGHT(UTF8_VAR, 10, CODEUNITS16),
     RIGHT(UTF8_VAR, 10, CODEUNITS32),
     RIGHT(UTF8_VAR, 10, OCTETS)
   FROM SYSIBM.SYSDUMMY1
```
returns the values '&N~ABbbbb', '&N~ABbbbbb', and '&N~ABb', respectively, where 'b' represents the blank character.
Assume that the variable UTF16_VAR, with a length attribute of 20 code units, contains the UTF-16BE representation of the string.
```
   SELECT RIGHT(UTF16_VAR, 2, CODEUNITS16),
     RIGHT(UTF16_VAR, 2, CODEUNITS32),
     RIGHT(UTF16_VAR, 2, OCTETS))
   FROM SYSIBM.SYSDUMMY1
```
returns the values 'AB', 'AB', and 'B', respectively.
```
   SELECT RIGHT(UTF16_VAR, 5, CODEUNITS16),
     RIGHT(UTF16_VAR, 5, CODEUNITS32),
     RIGHT(UTF16_VAR, 6, OCTETS)
   FROM SYSIBM.SYSDUMMY1
```
returns the values '?N~AB', '&N~AB', and '~AB', respectively, where '?' is the standalone low surrogate X'DD1E'.
```
   SELECT RIGHT(UTF16_VAR, 10, CODEUNITS16),
     RIGHT(UTF16_VAR, 10, CODEUNITS32),
     RIGHT(UTF16_VAR, 10, OCTETS)
   FROM SYSIBM.SYSDUMMY1
```
returns the values '&N~ABbbbb', '&N~ABbbbbb', and '?N~AB', respectively, where 'b' represents the blank character and '?' is X'DD1E'.

	'&'	'N'	'~'	'A'	'B'
UTF-8	X'F09D849E'	X'4E'	X'CC83'	X'41'	X'42'
UTF-16BE	X'D834DD1E'	X'004E'	X'0303'	X'0041'	X'0042'