HP 3000 Manuals HP 3000 Manuals
String Functions
String functions return partial values or attributes of character and
BINARY (including LONG) string data.
Scope
SQL Data Manipulation Statements
SQL Syntax
[REV BEG]
{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.
[REV END]
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:
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) |
| | | |
--------------------------------------------------------------
(1) 3996 is the maximum length of a VARBINARY data type
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
2. 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