String functions perform conversion, extraction, or manipulation operations on strings, or return information about strings.
When working in a multibyte character set, check carefully whether the function being used returns information concerning characters or bytes.
Most of the string functions accept binary data (hexadecimal strings) in the string-expr parameter, but some of the functions, such as LCASE, UCASE, LOWER, and LTRIM, expect the string expression to be a character string.
Unless you supply a constant LENGTH argument to a function that produces a VARCHAR result (such as SPACE or REPEAT), the default length is the maximum allowed. See the “Field Size” column in Table 6-1.
Sybase IQ queries containing one or more of these functions might return one of the following errors:
ASA Error -1009080: Key doesn't fit on a single database page: 65560(4, 1)
ASA Error -1009119: Record size too large for database page size
For example:
SELECT COUNT(*) FROM test1 a WHERE (a.col1 + SPACE(4-LENGTH(a.col1)) + a.col2 + space(2- LENGTH(a.col2))) IN (SELECT (b.col3) FROM test1 b);
To avoid such errors, cast the function result with an appropriate maximum length; for example:
SELECT COUNT(*) FROM test1 a WHERE (a.col1 + CAST(SPACE(4-LENGTH(a.col1)) AS VARCHAR(4)) + a.col2 + CAST(SPACE(2-LENGTH (a.col2)) AS VARCHAR(4))) IN (SELECT (b.col3) FROM test1 b);
The errors are more likely with an IQ page size of 64K or a multibyte collation.
Table 4-9 lists string functions and their parameters.
String function |
Parameters |
|---|---|
ASCII |
( string-expr ) |
BIT_LENGTH |
( column-name ) |
BYTE_LENGTH |
( string-expr ) |
CHAR |
( integer-expr ) |
CHAR_LENGTH |
( string-expr ) |
CHARINDEX |
( string-expr1, string-expr2 ) |
DIFFERENCE |
( string-expr1, string-expr2 ) |
GRAPHICAL_PLAN |
( string-expr ) |
HTML_PLAN |
( string-expr ) |
INSERTSTR |
( numeric-expr, string-expr1, string-expr2 ) |
LCASE |
( string-expr ) |
LEFT |
( string-expr, numeric-expr ) |
LEN |
( string-expr ) |
LENGTH |
( string-expr ) |
LOCATE |
( string-expr1, string-expr2 [ , numeric-expr ] ) |
LOWER |
( string-expr ) |
LTRIM |
( string-expr ) |
OCTET_LENGTH |
( column-name ) |
PATINDEX |
( '%pattern%', string_expr ) |
REPEAT |
( string-expr, numeric-expr ) |
REPLACE |
( original-string, search-string, replace-string ) |
REVERSE |
( expression | uchar_expr ) |
REPLICATE |
( string-expr, integer-expr ) |
RIGHT |
( string-expr, numeric-expr ) |
RTRIM |
( string-expr ) |
SIMILAR |
( string-expr1, string-expr2 ) |
SORTKEY |
( string-expression [, { collation-id | collation-name [(collation-tailoring-string)] } ] ) |
SOUNDEX |
( string-expr ) |
SPACE |
( integer-expr ) |
STR |
( numeric_expr [ , length [ , decimal ] ] ) |
STR_REPLACE |
( string_expr1, string_expr2, string_expr3 ) |
STRING |
( string1 [ , string2, …, string99 ] ) |
STUFF |
( string-expr1, start, length, string-expr2 ) |
SUBSTRING |
( string-expr, integer-expr [ , integer-expr ] ) |
TRIM |
( string-expr ) |
UCASE |
( string-expr ) |
UPPER |
( string-expr ) |
