8.5.5 RIGHT

(1) Specification format

scalar-function-RIGHT ::= RIGHT(source-character-string-data,extraction-length)
 
  source-character-string-data ::= value-expression
  extraction-length ::= value-expression

To Page Top

(2) Explanation of specification format

source-character-string-data:

Specifies the source character string data.

Note the following rules:

• Specify the source character string data in the form of a value expression. For details about value expressions, see 7.21 Value expression.

• Specify CHAR, VARCHAR, or STRING type data for the source character string data.

• You cannot specify a dynamic parameter by itself for the source character string data.

extraction-length:

Specifies the number of characters to extract. The specified number of characters will be extracted from the end of the source character string data.

The following specification rules apply:

• Specify the extraction length in the form of a value expression. For details about value expressions, see 7.21 Value expression.

• Specify data of type INTEGER, BIGINT, or SMALLINT for the number of characters to extract.

• If you specify a dynamic parameter alone for the extraction length, the assumed data type of the dynamic parameter is BIGINT type^#.

  #

  If the integer data type format is a legacy format, it will be an INTEGER type.

The following example illustrates the result of executing the scalar function RIGHT.

Example:

Extract three characters from the end of the character string ABCDEF.

RIGHT('ABCDEF',3) → 'DEF'

To Page Top

(3) Rules

1. The data type and data length of the execution result are shown in the following table.

   Table 8‒15: Data type and data length of the execution result of the scalar function RIGHT

   Data type and data length of the source character string data	Data type and data length of the execution result
   CHAR(n)	VARCHAR(n)
   VARCHAR(n)
   STRING	STRING

   Legend:

   n: Maximum length of the source character string data

   The number of characters to be extracted is as follows.

   MIN (extraction length, number of characters in the source character string data)

2. If "extraction length > the number of characters in the source character string data", data equivalent to the number of characters in the source character string data is returned.

3. In the following cases, the result is data with an actual length of 0 bytes.

   • When the length of the character string in the execution result is 0

   • When the source character string data is 0 bytes or 0 characters in actual length

4. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

5. In the following cases, the execution result will be a null value:

   • If either the source character string data or the extraction length is a null value

   • If the extraction length is a negative value (it will be a null value regardless of the specification of the source character string data)

To Page Top

(4) Example

Example:

Retrieve rows from table T1 where the data in column C1 ends with the three-character string 14B.

SELECT * FROM "T1"
    WHERE RIGHT("C1",3)='14B'

To Page Top