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



______________________________________________________________________________________

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