 |
» |
|
|
|
String functions return partial values or attributes of character and
BINARY (including LONG) string data. Scope | |
SQL Data Manipulation Statements SQL Syntax | |
{ STRING_LENGTH (StringExpression) SUBSTRING (StringExpression,StartPosition,Length) } Parameters | |
- STRING_LENGTH
returns an integer indicating the length of the parameter.
If StringExpression is a fixed length string type, STRING_LENGTH
will return the fixed length.
If StringExpression is a variable length string,
the actual length of the string will be returned.
- StringExpression
is an expression of a string type. See the "Expression" section in this chapter for the syntax. The expression must be a CHAR, VARCHAR, BINARY, VARBINARY, Long Binary, or Long VARBINARY data type. For example, the following are acceptable:
VendorName
'Applied Analysis'
SUBSTRING(VendorName,1,10)
|
- SUBSTRING
returns the portion of the SourceString parameter
which begins at StartPosition and is Length bytes long.
- StartPosition
is an integer constant or expression. See the "Expression" section in
this chapter for this syntax.
- Length
is an integer constant or expression. See the "Expression" section in
this chapter for this syntax. The following are examples of
acceptable lengths:
5
STRING_LENGTH(VendorName)-28
|
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:
Table 8-2 Data Type Returned by SUBSTRING | StringExpression | SUBSTRING | SUBSTRING |
|---|
| Data Type | Data Type | 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 [1] | | LONG VARBINARY | VARBINARY | 3996 [1] |
Examples | |
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
|
SUBSTRING example For every row in PartsIllus, the PartNumber and the first 350 bytes of the PartPicture are inserted into the DataBank table:
CREATE TABLE DataBank
(IdNumber INTEGER,
Data VARBINARY(1000))
INSERT INTO DataBank
SELECT PartNumber, SUBSTRING(PartPicture,1,350)
FROM PartsIllus
|
Display a substring of the PartPicture column in the PartsIllus table if the Data column in the DataBank table contains more than 133 bytes:
SELECT DATA
FROM DataBank
WHERE STRING_LENGTH(Data) > 133
|
|
