CHAR_LENGTH, OCTET_LENGTH, BIT_LENGTH Functions

Functions Defined by the SQL Standard

UPPER, LOWER Functions

SUBSTRING Function (SQL)

SUBSTRING ( str FROM start [ FOR length ] )

The SUBSTRING function picks a substring from a character or a binary string, CLOB or BLOB str (including UNICODE strings). This substring begins at the start position and may extend up to the end of the str argument. If the length is specified, the result string will not be longer than this length.

The start and length parameters must be integer numbers. The string positions are numbered from one. If some argument is NULL, the result will be NULL as well.

If the length is set, it is an integer constant and does not exceeds the maximum string length value, then:

• If str is a character or binary string with a maximum length longer than length, the function result type will be the same and the maximum length is the length.
• If str is BLOB, the function value is a binary string with a maximum length of length.
• If str is CLOB, the function value is a binary string with a maximum length of length;

In other cases the function value type is the same as the str type. This allows you to use the SUBSTING function to create an effective temporary index.

Usage Example:

Test whether a variable value begins with the character X:

IF SUBSTRING(Item FROM 1 FOR 1) = 'X' THEN 
    UPDATE  SET amount = amount*1.1 WHERE CURRENT OF curamount;

See

• CHAR_LENGTH
• POSITION

CHAR_LENGTH, OCTET_LENGTH, BIT_LENGTH Functions

Functions Defined by the SQL Standard

UPPER, LOWER Functions