![]() |
SQL-J Language Reference
|
Reference Manual |
SUBSTRINGThe SUBSTRING 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 maximum length of the result is the maximum length of the source expression. Syntax
SUBSTRING( { CharacterExpression | BitExpression } 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.) An exception is thrown if lengthOfString is specified and it is negative. 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. If lengthOfString is not specified, SUBSTRING returns the substring of the source from the start position to the end of the source string. If lengthOfString is specified, SUBSTRING returns a VARCHAR or BIT VARYING with a length of lengthOfString starting at the startPosition. Odd CasesThe function returns NULL if the source expression or startPosition evaluates to null or if lengthOfString is specified and it evaluates to null. If startPosition evaluates to an integer that is greater than the length of the source, an empty character or bit expression is returned. startPosition is allowed to be negative. If it is negative and startPosition + lengthOfString <= 0, an empty VARCHAR or VARBIT is returned. If startPosition is negative and startPosition + lengthOfString > 0, a character or bit expression of length startPosition + lengthOfString starting at position 1 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. SUBSTRING Examples
-- returns 34
-- returns 34
-- returns 345
-- returns '12' --0 is converted to 1
-- returns 1
-- exception is thrown
-- returns null |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |