8.5.2 LEFT

(1) Specification format

scalar-function-LEFT ::= LEFT(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.

The following rules apply:

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

• Specify CHAR or VARCHAR 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 beginning of the source character string data.

The following rules apply:

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

• Specify an integer (data of type INTEGER or SMALLINT) for the extraction length.

• If a dynamic parameter is specified by itself for the extraction length, the assumed data type of the dynamic parameter will be INTEGER.

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

Example

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

LEFT('ABCDEF',3) → 'ABC'

To Page Top

(3) Rules

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

   Table 8‒12: Data type and data length of the execution result of the scalar function LEFT

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

   Legend:

   n: Maximum length of the source character string data

   The number of characters that are extracted is determined as follows:

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

2. If the extraction length is greater than the number of characters in the source character string data, the amount of data returned will be the number of characters in the source character string data.

3. In the following cases, the execution result will be data whose actual length is 0 bytes:

   • If the length of the character string of the execution result is 0

   • If the actual length of the source character string data is 0 bytes or 0 characters

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 extraction length has a null value

   • If the extraction length has a negative value (the result will be the null value regardless of what is specified for the source character string data)

To Page Top

(4) Example

Example:

Retrieve rows from table T1 where the data in column C1 begins with the three-character string A15.

SELECT * FROM "T1"
    WHERE LEFT("C1",3)='A15'

To Page Top