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.




Returns the ASCII code of the left-most character of string_exp as an integer.


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.


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.


Converts all upper case characters in string_exp to lower case.


Returns the leftmost count characters of 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.


Returns the characters of string_exp, with leading blanks removed.


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.


Returns the last count characters of string_exp.


Returns the characters of string_exp with trailing blanks removed.


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.


Returns a character string consisting of count spaces.


Returns a character string that is derived from string_exp beginning at the character position specified by start for length characters.


Returns the characters of string_exp with leading and trailing blanks removed.


Converts all lower-case characters in string_exp to upper case.


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


Copyright BASIS International Ltd. BBj®, Visual PRO/5®, PRO/5®, and BBx® are registered trademarks.