String Functions

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.

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.

This table lists string functions and their parameters.

String functions

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-idcollation-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 Unstructured Data Analytics in Sybase IQ > Function Support.

Related concepts
Physical Limitations