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 LONG VARCHAR result (such as SPACE or REPEAT), the default length is the maximum allowed.
SAP 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
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);
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 a page size of 64K or a multibyte collation.
This table 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 ) |
For information on string functions that support the LONG BINARY and LONG VARCHAR data types, see Function Support in Unstructured Data Analytics.