8.5.8 SUBSTR

(1) Specification format

scalar-function-SUBSTR ::= SUBSTR(source-character-string-data, start-position[,extraction-length])
 
  source-character-string-data ::= value-expression
  start-position ::= 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.

start-position:

Specifies the starting character position from which to extract character string data.

If you specify a value greater than or equal to 0 for the start position, the value represents the position from the beginning of the source character string data. For example, if the start position is 2, the extraction will start at the second character.

If you specify a negative value for the start position, the value represents a position from the end of the source character string data. For example, if the start position is -2, the extraction will start at the second character from the end.

The following rules apply:

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

• Specify an integer for the start position (INTEGER or SMALLINT type data).

• If you specify 0 for the start position, a start position of 1 is assumed.

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

extraction-length:

Specifies the number of characters to extract.

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 greater than or equal to 0 (data of type INTEGER or SMALLINT) for the extraction length.

• If no extraction length is specified, when the source character string data is CHAR type, it extracts from the start position to the last character of the defined length.When the source character string data is VARCHAR type, it extracts from the start position to the last character of the actual data.

• 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 examples illustrate the result of executing the scalar function SUBSTR.

Examples

• Extract three characters starting from the second character from the beginning of the character string ABCDEF.

  SUBSTR('ABCDEF',2,3) → 'BCD'

• Extract two characters starting from the third character from the end of the character string ABCDEF.

  SUBSTR('ABCDEF',-3,2) → 'DE'

To Page Top

(3) Rules

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

2. In the following cases, the execution result will be 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 or the start position)

   • If the source character string data, start position, or extraction length is a null value

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

   Table 8‒18: Data type and data length of the execution result of the scalar function SUBSTR

   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)

   Legend:

   n: Maximum length of the source character string data

4. The following table shows the number of characters that can be extracted by the scalar function SUBSTR.

   Table 8‒19: Number of characters that can be extracted by the scalar function SUBSTR

   Specification of the scalar function SUBSTR		Number of characters that can be extracted
   Specification of extraction length	Value specified for start position
   Specified	Positive value	MAX{0, MIN(extraction length, number of characters in source character string data - start position + 1)}
   	0	MIN(extraction length, number of characters in source character string data)
   	Negative value	MIN(extraction length, absolute value of the start position, number of characters in source character string data)
   Omitted	Positive value	MAX(0, number of characters in source character string data - start position + 1)
   	0	number of characters in source character string data
   	Negative value	MIN(absolute value of the start position, number of characters in source character string data)

5. 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

   • If the specified start position satisfies either of the following inequalities:

     start position > number of characters in source character string data

     start position < -number of characters in source character string data

6. If the number of characters in the source character string data, starting from the start position, is less than the extraction length, all of the source character string data, starting from the start position, is returned.

   Example

   SUBSTR('ABCDEF',5,3) → 'EF'

To Page Top

(4) Examples

Example 1:

Retrieve rows from table T1 where the data in column C1 contains the three-character substring 150 starting from the second character.

SELECT * FROM "T1"
    WHERE SUBSTR("C1",2,3)='150'

Example 2:

Retrieve rows from table T1 where the data in column C1 contains the two-character substring 01 starting from the second character from the end.

SELECT * FROM "T1"
    WHERE SUBSTR("C1",-2,2)='01'

To Page Top