User's Guide Part VI. SQL Anywhere Reference Chapter 42. Watcom-SQL FunctionsString functions perform conversion, extraction or manipulation operations on strings or return information about strings.
When working in a multi-byte character set, check carefully whether the function being used returns information concerning characters or bytes.
String function:
ASCII ( string-expr )
| BYTE_LENGTH ( string-expr )
| BYTE_SUBSTR ( string-expr, integer-expr [, integer-expr ] )
| CHAR ( string-expr )
| DIFFERENCE (string-expr, string-expr )
| INSERTSTR ( numeric-expr, string-expr, string-expr )
| LCASE ( string-expr )
| LEFT ( string-expr, numeric-expr )
| LENGTH ( string-expr )
| LOCATE ( string-expr, string-expr [, numeric-expr ] )
| LTRIM ( string-expr )
| PATINDEX ( '%pattern%', string_expr )
| REPEAT ( string-expr, numeric-expr )
| RIGHT ( string-expr, numeric-expr )
| RTRIM ( string-expr )
| SIMILAR ( string-expr, string-expr )
| SOUNDEX ( string-expr )
| SPACE ( integer-expr )
| STRING ( string-expr [, ...] )
| SUBSTR ( string-expr, integer-expr [, integer-expr] )
| TRIM ( string-expr )
| UCASE ( string-expr )
ASCII( string-expr ) Returns the integer ASCII value of the first byte in string-expr, or 0 for the empty string.
BYTE_LENGTH( string-expr ) Returns the number of bytes in the string string-expr.
BYTE_SUBSTR( string-expr, start [, length] ) Returns the substring of string-expr starting at the given start position (origin 1), in bytes. If the length is specified, the substring is restricted to that number of bytes. Both start and length can be negative. A negative starting position specifies a number of bytes from the end of the string instead of the beginning. A positive length specifies that the substring ends length bytes to the right of the starting position, while a negative length specifies that the substring ends length bytes to the left of the starting position. Using appropriate combinations of negative and positive numbers, you can get a substring from either the beginning or end of the string.
CHAR( numeric-expr ) Returns the character with the ASCII value numeric-expr. The character in the current character set corresponding to the supplied numeric expression modulo 256 is returned. If you are using multi-byte character sets, CHAR may not return a valid character.
DIFFERENCE( string-expr1, string-expr2 ) Returns the difference in the soundex values of string-expr1 and string-expr2.
INSERTSTR( numeric-expr, string-expr1, string-expr2) Inserts string-expr2 in string-expr1 at character position numeric-expr.
LCASE( string-expr ) Converts all characters in string-expr to lower case.
LEFT( string-expr, numeric-expr ) Returns the leftmost numeric-expr characters of string-expr.
LENGTH( string-expr ) Returns the number of characters in the string string-expr. If string-expr is of binary data type, the LENGTH function behaves as BYTE_LENGTH.
LOCATE( string-expr1, string-expr2 [, numeric-expr ] ) Returns the character offset (base 1) into the string string-expr1 of the first occurrence of the string string-expr2. If numeric-expr is specified, the search will start at that offset into the string.
The first string can be a long string (longer than 255 bytes), but the second is limited to 255 bytes. If a long string is given as the second argument, the function returns a NULL value. If the string is not found, 0 is returned. Searching for a zero-length string will return 1. If any of the arguments are NULL, the result is NULL.
LTRIM( string-expr ) Returns string-expr with leading blanks removed.
PATINDEX( '%pattern%', string-expr ) Returns an integer representing the starting position in characters of the first occurrence of pattern in the specified string expression, or a zero if pattern is not found. If the leading percent wild card is omitted, PATINDEX returns one (1) if pattern occurs at the beginning of the string, and zero if not. If the trailing percent wild card is omitted, PATINDEX returns one (1) if pattern occurs at the end of the string, and zero if not. If pattern starts with a percent wild card, then the two leading percent wild cards are treated as one.
REPEAT( string-expr, integer-expr ) Returns a string comprised of integer-expr instances of string-expr, concatenated together.
RIGHT( string-expr, numeric-expr ) Returns the rightmost numeric-expr characters of string-expr.
RTRIM( string-expr Returns string-expr with trailing blanks removed.
SIMILAR( string-expr1, string-expr2 ) Returns an integer between 0 and 100 representing the similarity between the two strings. The result can be interpreted as the percentage of characters matched between the two strings (100 percent match if the two strings are identical).
This function can be very useful for correcting a list of names (such as customers). Some customers may have been added to the list more than once with slightly different names. Join the table to itself and produce a report of all similarities greater than 90 percent but less than 100 percent.
SOUNDEX( string-expr ) Returns a number representing the sound of the string-expr. Although it is not perfect, soundex will normally return the same number for words which sound similar and start with the same letter. For example:
soundex( 'Smith') = soundex( 'Smythe' )
The soundex function value for a string is based on the first letter and the next three consonants other than H, Y, and W. Doubled letters are counted as one letter. For example,
soundex( 'apples' )
is based on the letters A, P, L and S. Multi-byte characters are ignored by the SOUNDEX function.
STRING( string1, [ string2, .., string99 ] ) Concatenates the strings into one large string. NULL values are treated as empty strings (''). Any numeric or date parameters are automatically converted to strings before concatenation. The STRING function can also be used to force any single expression to be a string by supplying that expression as the only parameter.
SUBSTR( string-expr, start [, length] ) Returns the substring of string-expr starting at the given start position (origin 1). If the length is specified, the substring is restricted to that length. Both start and length can be negative. A negative starting position specifies a number of characters from the end of the string instead of the beginning. A positive length specifies that the substring ends length characters to the right of the starting position, while a negative length specifies that the substring ends length characters to the left of the starting position. Using appropriate combinations of negative and positive numbers, you can easily get a substring from either the beginning or end of the string. If string-expr is of binary data type, the SUBSTR function behaves as BYTE_SUBSTR.
TRIM( string-expr ) Returns string-expr with both leading and trailing blanks removed.
UCASE( string-expr ) Converts all characters in string-expr to uppercase.