String Functions

The table below lists the string functions included in the ODBC scalar function set that can be used with the BASIS ODBC Driver. The string functions are 1-based (the first character in the string is character 1).

Arguments denoted as string_exp can be the name of a column (of type CHAR), the result of another scalar function that returns a character string value, or a character-string literal.

Arguments denoted as start, length, or count can be a numeric literal or the result of another scalar function.

Function	Description
ASCII(string_exp)	Returns the ASCII code of the left-most character of string_exp as an integer.
CHAR(code)	Returns the character that has the ASCII code value returns the character specified by code. The code value should be between 0 and 255; otherwise, the return value is data source-dependent.
CONCAT(string_expl, string_exp2)	Returns a character string that is the result of concatenating string_exp2 to string_exp1. The resulting string is DBMS dependent. For example, if the column represented by string string_exp1 contains a NULL value, DB2 would return NULL, but SQL Server would return the non-NULL string.
DIFFERENCE(char,char)	Returns and integer value that indicates the difference between the values returned by the SOUNDEX function for string_exp1 and string_exp2.
INSERT(string_expl, start,length,string_exp2)	Returns a character string where length characters have been deleted from string_exp1 beginning at start and where string_exp2 has been inserted into string_exp1, beginning with start.
LCASE(string_exp)	Converts all upper case characters in string_exp to lower case.
LEFT(string_exp,count)	Returns the leftmost count characters of string_exp.
LENGTH(string_exp)	Returns the length of string_exp.
LOCATE(string_exp1, string_exp2[,start])	Returns the starting position of the first occurrence of string_exp1 within string_exp2. The search for the first occurrence of string_exp1 begins with the first character position in string_exp2, unless the optional start argument is specified. If start is specified, the search begins with the character position indicated by the value of start. The first character position string_exp2 is indicated by the value 1. If string_exp1 is not found within string_exp2, the value 0 is returned.
LTRIM(string_exp)	Returns the characters of string_exp, with leading blanks removed.
REPEAT(string_exp,count)	Returns a character string composed of string_exp repeated count times.
REPLACE(string_exp1, string_exp2, string_exp3)	Replaces each occurrence of string_exp2 in string_exp1 with string_exp3.
RIGHT(string_exp,count)	Returns the last count characters of string_exp.
RTRIM(string_exp)	Returns the characters of string_exp with trailing blanks removed.
SOUNDEX(string_exp)	Returns a data source-dependent character string representing the sound of the words in string_exp. For example, SQL Server returns a four-digit SOUNDEX code.
SPACE(count)	Returns a character string consisting of count spaces.
SUBSTRING(string_exp,start,length)	Returns a character string that is derived from string_exp beginning at the character position specified by start for length characters.
TRIM(string_exp)	Returns the characters of string_exp with leading and trailing blanks removed.
UCASE(string_exp)	Converts all lower-case characters in string_exp to upper case.

Example

Select RTRIM(last_name) + ' , ' + first_name from customer