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.

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.

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.

Note the following rules:

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

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

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

• If you specify a dynamic parameter alone for the start position, 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.

extraction-length:

Specifies the number of characters to extract.

Note the following rules:

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

• Specify an integer greater than or equal to 0 (data of type INTEGER, BIGINT, or SMALLINT) for the number of characters to extract

• 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 or STRING type, it extracts from the start position to the last character of the actual data.

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

Example:

• 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 is a negative value (the value is null regardless of the source character string data or the starting position)

   • If either the source character string data, starting position, or the 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)
   STRING	STRING

   Legend: n: Maximum length of the source character string data

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

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

   Scalar function SUBSTR specification		Number of characters extracted
   Extraction length specification	Start position specification
   Specified	Positive value	MAX {0, MIN (extraction length, number of characters in the source character string data - start position + 1)}
   	0	MIN (extraction length, number of characters in the source character string data)
   	Negative value	MIN (extraction length, absolute value of the start position, number of characters in the source character string data)
   Omitted	Positive value	MAX (0, number of characters in the source character string data - start position + 1)
   	0	Number of characters in the source character string data
   	Negative value	MIN (absolute value of the starting position, number of characters in the source character string data)

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

   • If the following values are specified for the start position

     start position > number of characters in the source character string data

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

6. If the "number of characters in the source character string data < extraction length", all character data after the start position of the source character string data 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