SUBSTR function

Functions

SUBSTR{START[,SIZE[,ADJUST]]}

This function returns the character string of SIZE bytes long starting from the position specified as START.

= Remarks =

The SUBSTR function (if START is positive) behaves similar to but different from the substring extraction based on the start position and output size specified in the output settings.

The behaviors are different on the following aspects:
- In the substring extraction based on the output settings, if the character string is type N, the extraction is performed before shift codes are added.
- In the SUBSTR function, if the character string is type N, the extraction is performed after the shift codes are added.
In the substring extraction based on the output settings, the start position is counted from 1. In the SUBSTR function, the start position is counted from 0.

Parameters

START

Specifies the start position as a number (0 and negative value are also allowed). (This parameter cannot be omitted.)

For a positive value or 0:

The function returns the character string of SIZE bytes long starting from the start position counted from the beginning and specified by START.

Example:

In the case of the character string "abcdef", the start position 0 is "a" and the start position 2 is "c".

If the start position exceeds the length of the target character string, a character string of 0 byte is returned.

For a negative value:

The function returns a character string of SIZE bytes long starting from the start position counted from the end and specified by START until the end. If the start position exceeds the length of the target character string, the first position is used as the start position, and therefore the character string is extracted starting from the beginning of the target character string.

SIZE

Specifies the number of bytes you want to extract. (This parameter can be omitted.)

If you omit this parameter, the character string starting from the start position until the end is returned.

If you enter 0 or a negative value, the function results in an error when executed.

If the length of the character string starting from the start position until the end is less than the number of bytes specified by SIZE, the character string from the start position until the end is returned (no error occurs).

ADJUST

This parameter is used to adjust the start position and the end position of the variable-length characters (fields of type M, CSV character strings, and XML character strings). (This parameter can be omitted.)

If you omit this parameter, the start position and the end position of the variable-length characters are not adjusted.

Table A.13 Start and end positions of variable-length characters
Setting value	Meaning
LL	If the start position is somewhere between the beginning and end of a character code, the start position moves to the left (toward the beginning). If the end position is somewhere between the beginning and end of a character code, the end position moves to the left (toward the beginning).
LR	If the start position is somewhere between the beginning and end of a character code, the start position moves to the left (toward the beginning). If the end position is somewhere between the beginning and end of a character code, the end position moves to the right (toward the end).
RL	If the start position is somewhere between the beginning and end of a character code, the start position moves to the right (toward the end). If the end position is somewhere between the beginning and end of a character code, the end position moves to the left (toward the beginning).
RR	If the start position is somewhere between the beginning and end of a character code, the start position moves to the right (toward the end). If the end position is somewhere between the beginning and end of a character code, the end position moves to the right (toward the end).
Other than the above	An error occurs.

Note

Even if the start position changes, the size does not change. However, if the end position changes, the size changes.

If the end position moves to the left (toward the beginning), the size decreases accordingly.
If the end position moves to the right (toward the end), the size increases accordingly.

= Remarks =

When a code conversion is performed, the size might change even if SIZE and ADJUST are set.

The setting for ADJUST is applicable to the following input encodings only: UTF-8, Shift JIS, and EUC. An error occurs if any other encoding is used for the input.

Example

= Remarks =

The character encoding used in this example is Shift JIS.

1. A character string that begins from the first position starting from the beginning (sixth when counted from the end) and has 3 bytes is extracted.

Figure A.41 Example of using the SUBSTR function ((1) From beginning)

Figure A.42 Example of using the SUBSTR function ((1) From end)

2. The character string is extracted from the third position (fourth when counted from the end) starting from the beginning.

Figure A.43 Example of using the SUBSTR function ((2) From beginning)

Figure A.44 Example of using the SUBSTR function ((2) From end)

3. If the length of the character string starting from the start position until the end is less than the number of bytes specified by SIZE, the character string from the start position until the end is extracted.

Figure A.45 Example of using the SUBSTR function (3)

4. If START is a positive value that exceeds the length of the target character string, no character string is extracted.

Figure A.46 Example of using the SUBSTR function (4)

5. If START is a negative value that negatively exceeds the length of the target character string, the character string is extracted starting from the beginning.

Figure A.47 Example of using the SUBSTR function (5)

6. The example below is the case where LL is selected as ADJUST and the character string of 3 bytes long starting from the first byte from the beginning is specified to be extracted. In such a case, if the start position falls within a kanji code, then the start position is shifted left to the delimiter. Also, if the end position falls within a kanji code, the end position is shifted left to the delimiter. This results in the character string 'あ' being extracted.

Figure A.48 Example of using the SUBSTR function (6)

7. The example below is the case where LR is selected as ADJUST and the character string of 3 bytes long starting from the first byte from the beginning is specified to be extracted. In such a case, if the start position falls within a kanji code, then the start position is shifted left to the delimiter. On the other hand, if the end position falls within a kanji code, the end position is shifted right to the delimiter. This results in the character string 'あい' being extracted.

Figure A.49 Example of using the SUBSTR function (7)

8. The example below is the case where RL is selected as ADJUST and the character string of 3 bytes long starting from the first byte from the beginning is specified to be extracted. In such a case, if the start position falls within a kanji code, then the start position is shifted right to the delimiter. On the other hand, if the end position falls within a kanji code, the end position is shifted left to the delimiter. This results in the character string 'い' being extracted.

Figure A.50 Example of using the SUBSTR function (8)

9. The example below is the case where RR is selected as ADJUST and the character string of 3 bytes long starting from the first byte from the beginning is specified to be extracted. In such a case, if the start position falls within a kanji code, then the start position is shifted right to the delimiter. Also, if the end position falls within a kanji code, the end position is shifted right to the delimiter. This results in the character string 'いう' being extracted.

Figure A.51 Example of using the SUBSTR function (9)