IBM Integration Bus, Version 9.0.0.8 Operating Systems: AIX, HP-Itanium, Linux, Solaris, Windows, z/OS

See information about the latest product version

SUBSTRING function

SUBSTRING is a string manipulation function that manipulates all string data types (BIT, BLOB, and CHARACTER), and extracts characters from a string to create another string.

Syntax


>>-SUBSTRING--(--SourceExpression--+- FROM --StartPosition------+-->
                                   +- BEFORE --BeforeExpression-+   
                                   '- AFTER --AfterExpression---'   

>--+---------------------+--)----------------------------------><
   '- FOR --StringLength-'

Parameters must be of the following types:

SourceExpression, BeforeExpression, and AfterExpression can be BIT, BLOB, or CHARACTER.
StartPosition and StringLength can be INTEGER only.

StartPosition

If you specify StartPosition, SUBSTRING returns a new string of the same type as SourceExpression containing one contiguous sequence of characters that are extracted from SourceExpression, as specified by StartPosition and StringLength. If you do not specify StringLength, the sequence runs from StartPosition until the end of SourceExpression. The StartPosition can be negative, and together, the StartPosition and StringLength define a range. The result is the overlap between this range and the SourceExpression; the StringLength cannot be less than the StartPosition.

BeforeExpression

If you specify BeforeExpression, SUBSTRING returns a new string of the same type as SourceExpression containing one contiguous sequence of characters that are extracted from StringLength characters before the first occurrence of BeforeExpression within SourceExpression, up to (but not including) the first character of the first occurrence of BeforeExpression. If you do not specify StringLength, the sequence of characters is taken from the beginning of SourceExpression up to (but not including) the first character of the first occurrence of BeforeExpression. If the BeforeExpression string does not occur in SourceExpression, an empty (zero length) string is returned.

The BeforeExpression string must be of the same data type as SourceExpression.

AfterExpression

If you specify AfterExpression, SUBSTRING returns a new string of the same type as SourceExpression, containing one contiguous sequence of characters that are extracted from SourceExpression, beginning with the first character after the end of the first occurrence of AfterExpression until the end of SourceExpression (or StringLength characters, if specified). If the AfterExpression string does not occur in SourceExpression, an empty (zero length) string is returned.

The AfterExpression string must be of the same data type as SourceExpression.

If any parameter is NULL, the result is NULL. This is not a zero length string.

Examples:

SUBSTRING('Hello World!' FROM 7 FOR 4)

returns 'Worl'.

SUBSTRING('Hello World!' BEFORE 'World');

returns 'Hello '.

SUBSTRING('Hello World!' BEFORE 'World' FOR 3);

returns 'lo '.

SUBSTRING('Hello World!' BEFORE 'e');

returns 'H'.

SUBSTRING('Hello World!' AFTER 'World');

returns '!'.

SUBSTRING('Hello World!' AFTER 'W' FOR 2);

returns 'or'.

SUBSTRING('Hello World!' AFTER 'P');

returns ''.

ak05260_.htm |

Last updated Friday, 21 July 2017