ALLBASE/SQL Reference Manual (36216-90216)

256 Chapter8
Expressions
String Functions
Description
• The string functions can appear in an expression, a select list, or a search condition of
an SQL data manipulation statement.
• The string functions can be applied to any string data type, including binary and long
column data types.
• The string returned by the SUBSTRING function is truncated if (
StartPosition +
Length -1) is greater than the length of the StringExpression . Only ( Length -
StartPosition +1) bytes is returned, and a warning is issued.
•If
Length is a simple constant, the substring returned has a maximum length equal to
the value of the constant. Otherwise, the length and data type returned by the
SUBSTRING function depend on the data type of
StringExpression , as shown in the
following table:
Examples
1. STRING_LENGTH example
In the SELECT statement below, the PartsIllus table is searched for any row whose
PartPicture contains more than 10000 bytes of data, and whose PartName is longer
than 10 bytes.
CREATE TABLE PartsIllus
(PartName VARCHAR(16),
PartNumber INTEGER,
PartPicture LONG VARBINARY(1000000) in PartPictureSet)
IN PartsIllusSet
SELECT PartNumber, PartName
FROM PartsIllus
WHERE STRING_LENGTH(PartPicture) > 10000
AND STRING_LENGTH(PartName) > 10
Table 8-1. Data Type Returned by SUBSTRING
StringExpression Data Type SUBSTRING Data Type SUBSTRING Maximum Length
CHAR VARCHAR fixed length of SourceString
VARCHAR VARCHAR maximum length of SourceString
BINARY VARBINARY fixed length of SourceString
VARBINARY VARBINARY maximum length of SourceString
LONG BINARY VARBINARY
3996
a
a. 3996 is the maximum length of a VARBINARY data type
LONG VARBINARY VARBINARY 3996a