RIGHT scalar function

The RIGHT function returns the rightmost string of string-expression of length length, expressed in the specified string unit.

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

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, Boolean, 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. length must be greater than or equal to 0 (SQLSTATE 22011). If OCTETS is specified and the result is graphic data, the value must be an even number (SQLSTATE 428GC).

If length is not a constant and a string unit is not specified, then length must be less than or equal to the length attribute of string-expression (SQLSTATE 22011).

If length is not a constant and a string unit is specified, then length must be less than or equal to the value from table 1 (SQLSTATE 22011):

Table 1. Maximum value of `length` when a string unit is specified
String unit of `string-expression`	Specified string unit	Maximum value of `length` L = length attribute of `string-expression`
String unit of `string-expression`	Specified string unit	Maximum value of `length` L = length attribute of `string-expression`
OCTETS	OCTETS	L
OCTETS	CODEUNITS16	L/2
OCTETS	CODEUNITS32	L/4
CODEUNITS16	OCTETS	L*2
CODEUNITS16	CODEUNITS16	L
CODEUNITS16	CODEUNITS32	L/2
CODEUNITS32	OCTETS	L*4
CODEUNITS32	CODEUNITS16	L*2
CODEUNITS32	CODEUNITS32	L

If length is a constant:

if string-expressionis CHAR, VARCHAR, GRAPHIC or VARGRAPHIC, length must be less than or equal to 32 672 OCTETS, 16 336 CODEUNITS16 or 8 168 CODEUNITS32 (SQLSTATE 22011)
if string-expression is CLOB or DBCLOB, length must be less than or equal to 2147483647 OCTETS, 1 073 741 823 CODEUNITS16 or 536870911 CODEUNITS32 (SQLSTATE 22011)
if string-expression is BLOB, length must be less than or equal to 2147483647 OCTETS (SQLSTATE 22011)

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 string unit of string-expression determines the unit that is used. 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 about padding, see String assignments in Assignments and comparisons.

The result of the function is a varying-length string that 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
VARBINARY if string-expression is BINARY or VARBINARY
BLOB if string-expression is BLOB

The string unit of the result is the string unit of string-expression.The length attribute of the result depends on how length and string unit are specified:

If length is not a constant, then the length attribute of the result is the same as the length attribute of string-expression.
If length is a constant and a string unit is not specified, then the length attribute of the result is the maximum of length and the length attribute of string-expression.

If length is a constant and a string unit is specified, then the length attribute of the result is shown in Table 2:

Table 2. Length attribute of result when `length` is a constant and a string unit is specified
String unit of `string-expression`	Specified string unit	Maximum value of `length` L = length attribute of `string-expression`
OCTETS	OCTETS	max(L, `length`)
OCTETS	CODEUNITS16	max(L, `length` * 2)
OCTETS	CODEUNITS32	max(L, `length` * 4)
CODEUNITS16	OCTETS	max(L, `length` / 2)
CODEUNITS16	CODEUNITS16	max(L, `length`)
CODEUNITS16	CODEUNITS32	max(L, `length` * 2)
CODEUNITS32	OCTETS	max(L, `length` / 4)
CODEUNITS32	CODEUNITS16	max(L, `length` / 2)
CODEUNITS32	CODEUNITS32	max(L, `length`)

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

Example 1: 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.
Example 2: 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.

Example 3: The following statement returns a zero-length string.

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

Example 4: 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.

Example 5: 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

Example 6: 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 in different Unicode encoding forms in the following example:

& 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 stand-alone 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'