SQL-J Language Reference
Page 83 of 121

SUBSTR

The SUBSTR function acts on a character string expression or a bit string expression. The type of the result is a VARCHAR in the first case and BIT VARYING in the second case. The length of the result is the maximum length of the source type.

Syntax

SUBSTR({ CharacterExpression | BitExpression },
   startPosition [, lengthOfString ] )

startPosition and the optional lengthOfString are both integer expressions. (The first character or bit has a startPosition of 1; if you specify 0, Cloudscape assumes that you mean 1.)

A characterExpression is a CHAR, VARCHAR, or LONG VARCHAR data type, any built-in type that is implicitly converted to a string (except a bit expression), or any Java data type that directly maps to a built-in type that is implicitly converted to a string.

For character expressions, both startPosition and lengthOfString refer to characters. For bit expressions, both startPosition and lengthOfString refer to bits.

SUBSTR returns NULL if lengthOfString is specified and it is less than zero.

If startPosition is positive, it refers to position from the start of the source expression (counting the first character as 1). If startPosition is negative, it is the position from the end of the source.

If lengthOfString is not specified, SUBSTR returns the substring of the expression from the startPosition to the end of the source expression. If lengthOfString is specified, SUBSTR returns a VARCHAR or VARBIT of length lengthOfString starting at the startPosition.

Odd Cases

The function returns NULL if the source expression or startPosition evaluates to null or if lengthOfString is specified and it evaluates to null.

If the startPosition evaluates to an integer that is greater than the length of the source SUBSTR returns an empty value of the appropriate type.

It is conceivable that startPosition is negative and its absolute value is >= the length of the source expression. In this case, if lengthOfString is not specified, the entire source expression is returned. If lengthOfString is specified and startPosition + lengthOfString <= 0, an empty value of the appropriate type is returned. If lengthOfString is specified and startPosition + lengthOfString > 0, a value of length startPosition + lengthOfString starting at position 0 of the appropriate type is returned.

NOTE: SUBSTRING is very similar to SUBSTR. The different syntaxes are provided to allow compatibility with applications that are programmed for different vendors' syntaxes. The only functional difference between them is that they have different semantics for a negative startPosition and a negative lengthOfString.

SUBSTR Examples

-- returns 34
VALUES SUBSTR('12345', 3, 2)

-- returns 34
VALUES SUBSTR(12345..toString(), 3, 2)

-- returns 345
VALUES SUBSTR('12345', 3)

-- returns '12' --0 is converted to 1
VALUES SUBSTR('12345', 0, 2)

-- returns 45
VALUES SUBSTR('12345', -2, 3)

-- returns null
VALUES SUBSTR('12345', 3, -1)

-- returns null
SELECT SUBSTR(stringColumn, 1, 3)
FROM mytable
WHERE stringColumn IS NULL