|
|
String functions return partial values or attributes of character and BINARY
(including LONG) string data.
With the G3 release of ALLBASE/SQL and IMAGE/SQL, the supported SQL syntax has
been enhanced to include the following string manipulation functions:
UPPER, LOWER, POSITION, INSTR, TRIM, LTRIM and RTRIM. These
string functions allow you to manipulate or examine the CHAR and
VARCHAR values within the SQL syntax, allowing for more
sophisticated queries and data manipulation commands to be formed.
These string functions were designed to be compatible with functions
specified in the ANSI SQL '92 standard and functions used in ORACLE.
In cases where the ANSI SQL '92 standard and the ORACLE functions
were not compatible (such as the LTRIM and RTRIM in ORACLE
versus TRIM in the ANSI standard), both versions were implemented.
The specifications for each of these functions follows.
LOWER
Converts all the characters in stringexpr to lower case
Syntax
[LOWER (stringexpr)]
UPPER
Converts all the characters in stringexpr to upper case
Syntax
[UPPER (stringexpr)]
POSITION
Searches for the presence of the string stringexpr1 in the
string stringexpr2 and returns a numeric value that indicates
the position at which stringexpr1 is found in
stringexpr2.
Syntax
[POSITION (stringexpr,stringexpr2)]
INSTR
Searches stringexpr1 beginning with its nth
character for the mth occurrence of stringexpr2
and returns the position of the character in stringexpr1 that
is the first character of this occurrence. If n is negative,
Instr counts and searches backward from the end of stringexpr1.
The value of m must be positive. The default values of both
n and m are 1, meaning Instr begins searching
at the first character of stringexpr1 for the first occurrence
of stringexpr2. The return value is relative to the beginning
of stringexpr1 regardless of the value of n,
and is expressed in characters. If the search is unsuccessful (if
stringexpr2 does not appear m times after the
nth character of stringexpr1) the return value
is 0.
If n and m are not specified the function is
equivalent to the ANSI SQL-92 POSITION function, except that the syntax is
slightly different.
Syntax
[INST (stringexpr1,stringexpr2 [,n[,m]])]
LTRIM
LTRIM function trims the characters specified in
charset from the beginning of the string
stringexpr.
Syntax
[LTRIM (charset,stringexpr)]
RTRIM
RTRIM function trims the characters specified in
charset from the end of the string stringexpr.
Syntax
[RTRIM (charset,stringexpr)]
TRIM
TRIM function allows you to strip the characters specified in
charset from the beginning and/or the end of the string
stringexpr. If charset is not specified, then
blank characters would be stripped from stringexpr.
Syntax
[ TRIM ({ LEADING | TRAILING | BOTH} (,charset ,stringexpr)]
Example 1
SELECT LOWER (OWNER) || '.' || LOWER (NAME)
FROM SYSTEM.TABLE
WHERE NAME = UPPER ('vendors');
Returns "purchdb .vendors"
Example 2
SELECT POSITION ('world', 'hello world')
FROM SYSTEM.TABLE
WHERE NAME = UPPER('vendors');
Returns the numeric value 7
Example 3
SELECT INSTR ('hello world hello world', 'world', 5, 2)
FROM SYSTEM.TABLE
WHERE NAME = UPPER('vendors');
Returns the numeric value 18 (starting position of the second
occurrence of the string 'world').
Example 4
SELECT * FROM SYSTEM.TABLE
WHERE NAME = LTRIM ('?*', 'VENDORS?*???***')
AND OWNER = 'PURCHDB';
Returns the system table entry for PURCHDB.VENDORS
Example 5
SELECT TRIM (BOTH '?*' FROM '??**?*hello ?* world???*')
FROM SYSTEM.TABLE
WHERE NAME = 'VENDORS';
Returns 'hello ?* world'.
SQL Data Manipulation Statements
{ STRING_LENGTH (StringExpression)
SUBSTRING (StringExpression,StartPosition,Length)}
- 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
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-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 [1] |
LONG VARBINARY | VARBINARY | 3996a |
[1]
3996 is the maximum length of a VARBINARY data type |
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
|