RIGHT

The RIGHT function returns a string that consists of the specified number of rightmost bytes or specified string unit from a string.

string-expression

An expression that specifies the string from which the result is derived. The string must be any built-in string data type. A substring of string-expression is zero or more contiguous code points of string-expression. A partial surrogate character in the expression is replaced with a blank.

The string can contain mixed data. Depending on the units that are specified to evaluate the function, the result is not necessarily a properly formed mixed data character string.

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

integer

An expression that specifies the length of the result. The value must be an integer between 0 and n, where n is the length attribute of string-expression, expressed in the units that are either implicitly or explicitly specified.

The argument can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34) which is then assigned to an INTEGER value.

CODEUNITS16, CODEUNITS32, or OCTETS

Specifies the unit that is used to express integer. If string-expression is a character string that is defined as bit data, CODEUNITS16 and CODEUNITS32 cannot be specified. If string-expression is a graphic string, OCTETS cannot be specified. If string-expression is a binary string, CODEUNITS16, CODEUNITS32, and OCTETS cannot be specified.

CODEUNITS16: Specifies that integer is expressed in terms of 16-bit UTF-16 code units.
CODEUNITS32: Specifies that integer is expressed in terms of 32-bit UTF-32 code units.
OCTETS: Specifies that integer is expressed in terms of bytes.

The string-expression is effectively padded on the right with the necessary number of padding characters so that the specified substring of string-expression always exists. The encoding scheme of the data determines the padding character:

For ASCII SBCS data or ASCII mixed data, the padding character is X'20'.
For ASCII DBCS data, the padding character depends on the CCSID; for example, for Japanese (CCSID 301) the padding character is X'8140', while for simplified Chinese it is X'A1A1'.
For EBCDIC SBCS data or EBCDIC mixed data, the padding character is X'40'.
For EBCDIC DBCS data, the padding character is X'4040'.
For Unicode SBCS data or UTF-8 data (Unicode mixed data), the padding character is X'20'.
For UTF-16 data (Unicode DBCS data), the padding character is X'0020'.
For binary data, the padding character is X'00'.

The result of the function is a varying-length string with a length attribute that is the same as the length attribute of string-expression and a data type that depends on the data type of string-expression:

VARBINARY if string-expression is BINARY or VARBINARY
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 is determined from integer.

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

The CCSID of the result is the same as that of string-expression.

Note:

Syntax alternatives: STRRIGHT is a synonym for RIGHT.

Examples

Example 1: Assume that host variable ALPHA has a value of 'ABCDEF'. The following statement returns the value 'DEF', which are the three rightmost characters in ALPHA.

   SELECT RIGHT(ALPHA,3)
     FROM SYSIBM.SYSDUMMY1;

Example 2: The following statement returns a zero length string.

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

Example 3: FIRSTNME is a VARCHAR(12) column in table T1. When FIRSTNME has the 6-character string 'Jürgen' as a value:


   Function ...                         Returns ...
   RIGHT(FIRSTNME,5,CODEUNITS32)        'ürgen'  -- x'C3BC7267656E'
   RIGHT(FIRSTNME,5,CODEUNITS16)        'ürgen'  -- x'C3BC7267656E'
   RIGHT(FIRSTNME,5,OCTETS)             ' rgen'  -- x'207267656E'  A truncated string

Example 4: In the following example, the last invocation of the RIGHT function returns a partial surrogate character:

  
   Function ...                         Returns ...
   RIGHT('Jürgen',5,CODEUNITS32)        'ürgen'       -- x'C3BC7267656E'
   RIGHT('Jürgen',5,CODEUNITS16)        'ürgen'       -- x'C3BC7267656E'
   RIGHT('Jürgen',5,OCTETS)             ' rgen'       -- x'207267656E'  A truncated string
   HEX(RIGHT('Jürgen',5))               x'BC7267656E' -- A partial character followed by 'rgen'