Use string functions to manipulate string values in SELECT, INSERT, UPDATE, or DELETE queries. DBISAM's SQL supports the following string functions:

LOWER or LCASE Function

The LOWER or LCASE function converts all characters in a string value to lowercase. The syntax is as follows:

LOWER(column_reference or string constant)
LCASE(column_reference or string constant)

In the following example, the values in the NAME column appear all in lowercase:

SELECT LOWER(Name)
FROM Country

The LOWER or LCASE function can be used in WHERE clause string comparisons to cause a case-insensitive comparison. Apply LOWER or LCASE to the values on both sides of the comparison operator (if one of the comparison values is a literal, simply enter it all in lower case).

SELECT *
FROM Names
WHERE LOWER(Lastname) = 'smith'

LOWER or LCASE can only be used with string or memo columns or constants.

UPPER or UCASE Function

The UPPER or UCASE function converts all characters in a string value to uppercase. The syntax is as follows:

UPPER(column_reference or string constant)
UCASE(column_reference or string constant)
 

Use UPPER or UCASE to convert all of the characters in a table column or character literal to uppercase. In the following example, the values in the NAME column are treated as all in uppercase. Because the same conversion is applied to both the filter column and comparison value in the WHERE clause, the filtering is effectively case-insensitive:

SELECT Name, Capital, Continent
FROM Country
WHERE UPPER(Name) LIKE UPPER('PE%')
 

UPPER can only be used with string or memo columns or constants.

LENGTH Function

The LENGTH function returns the length of a string value as an integer value. The syntax is as follows:

LENGTH(column_reference or string constant)
 

In the following example, the length of the values in the Notes column are returned as part of the SELECT statement:

SELECT Notes, LENGTH(Notes) AS "Num Chars"
FROM Biolife

LENGTH can only be used with string or memo columns or constants.

SUBSTRING Function

The SUBSTRING function extracts a substring from a string. The syntax is as follows:

SUBSTRING(column_reference or string constant
         FROM start_index [FOR length])
SUBSTRING(column_reference or string constant,
         start_index[,length])

The second FROM parameter is the character position at which the extracted substring starts within the original string. The index for the FROM parameter is based on the first character in the source value being 1.

The FOR parameter is optional, and specifies the length of the extracted substring. If the FOR parameter is omitted, the substring goes from the position specified by the FROM parameter to the end of the string.

In the following example, the SUBSTRING function is applied to the literal string 'ABCDE' and returns the value 'BCD':

SELECT SUBSTRING('ABCDE' FROM 2 FOR 3) AS Sub
FROM Country

In the following example, only the second and subsequent characters of the NAME column are retrieved:

SELECT SUBSTRING(Name FROM 2)
FROM Country

SUBSTRING can only be used with string or memo columns or constants.

LEFT Function

The LEFT function extracts a certain number of characters from the left side of a string. The syntax is as follows:

LEFT(column_reference or string constant FOR length)
LEFT(column_reference or string constant,length)

The FOR parameter specifies the length of the extracted substring.

In the following example, the LEFT function is applied to the literal string 'ABCDE' and returns the value 'ABC':

SELECT LEFT('ABCDE' FOR 3) AS Sub
FROM Country

LEFT can only be used with string or memo columns or constants.

RIGHT Function

The RIGHT function extracts a certain number of characters from the right side of a string. The syntax is as follows:

RIGHT(column_reference or string constant FOR length)
RIGHT(column_reference or string constant,length)

The FOR parameter specifies the length of the extracted substring.

In the following example, the RIGHT function is applied to the literal string 'ABCDE' and returns the value 'DE':

SELECT RIGHT('ABCDE' FOR 2) AS Sub
FROM Country

RIGHT can only be used with string or memo columns or constants.

TRIM Function

The TRIM function removes the trailing or leading character, or both, from a string. The syntax is as follows:

TRIM([LEADING|TRAILING|BOTH] trimmed_char
    FROM column_reference or string constant)
TRIM([LEADING|TRAILING|BOTH] trimmed_char,
    column_reference or string constant)

The first parameter indicates the position of the character to be deleted, and has one of the following values:

The trimmed character parameter specifies the character to be deleted. Case-sensitivity is applied for this parameter. To make TRIM case-insensitive, use the UPPER or UCASE function on the column reference or string constant.

The FROM parameter specifies the column or constant from which to delete the character. The column reference for the FROM parameter can be a string column or a string constant.

The following are examples of using the TRIM function:

TRIM(LEADING '_' FROM '_ABC_') will return 'ABC_'
TRIM(TRAILING '_' FROM '_ABC_') will return '_ABC'
TRIM(BOTH '_' FROM '_ABC_') will return 'ABC'
TRIM(BOTH 'A' FROM 'ABC') will return 'BC'

TRIM can only be used with string or memo columns or constants.

LTRIM Function

The LTRIM function removes any leading spaces from a string. The syntax is as follows:

LTRIM(column_reference or string constant)

The first and only parameter specifies the column or constant from which to delete the leading spaces, if any are present. The following is an example of using the LTRIM function:

LTRIM('   ABC') will return 'ABC'

LTRIM can only be used with string or memo columns or constants.

RTRIM Function

The RTRIM function removes any trailing spaces from a string. The syntax is as follows:

RTRIM(column_reference or string constant)

The first and only parameter specifies the column or constant from which to delete the trailing spaces, if any are present. The following is an example of using the RTRIM function:

RTRIM('ABC   ') will return 'ABC'

RTRIM can only be used with string or memo columns or constants.

POS or POSITION Function

The POS or POSITION function returns the position of one string within another string. The syntax is as follows:

POS(string constant IN column_reference or string constant)
POSITION(string constant IN column_reference or string constant)
POS(string constant,column_reference or string constant)
POSITION(string constant,column_reference or string constant)

If the search string is not present, then 0 will be returned.

In the following example, the POS function is used to select all rows where the literal string 'ABC' exists in the Name column:

SELECT *
FROM Country
WHERE POS('ABC' IN Name) > 0

POS or POSITION can only be used with string or memo columns or constants.

OCCURS Function

The OCCURS function returns the number of occurrences of one string within another string. The syntax is as follows:

OCCURS(string constant
      IN column_reference or string constant)
OCCURS(string constant,
      column_reference or string constant)

If the search string is not present, then 0 will be returned.

In the following example, the OCCURS function is used to select all rows where the literal string 'ABC' occurs at least once in the Name column:

SELECT *
FROM Country
WHERE OCCURS('ABC' IN Name) > 0

OCCURS can only be used with string or memo columns or constants.

REPLACE Function

The REPLACE function replaces all occurrences of a given string with a new string within another string. The syntax is as follows:

REPLACE(string constant WITH new string constant
       IN column_reference or string constant)
REPLACE(string constant,new string constant,
       column_reference or string constant)

If the search string is not present, then the result will be the original table column or string constant.

In the following example, the REPLACE function is used to replace all occurrences of 'Mexico' with 'South America':

UPDATE biolife
SET notes=REPLACE('Mexico' WITH 'South America' IN notes)

REPLACE can only be used with string or memo columns or constants.

REPEAT Function

The REPEAT function repeats a given string a specified number of times and returns the concatenated result. The syntax is as follows:

REPEAT(column_reference or string constant
      FOR number_of_occurrences)
REPEAT(column_reference or string constant,
      number_of_occurrences)

In the following example, the REPEAT function is used to replicate the dash (-) character 60 times to use as a separator in a multi-line string:

UPDATE biolife
SET notes='Notes'+#13+#10+
REPEAT('-' FOR 60)+#13+#10+#13+#10+
'These are the notes'

REPEAT can only be used with string or memo columns or constants.

CONCAT Function

The CONCAT function concatenates two strings together and returns the concatenated result. The syntax is as follows:

CONCAT(column_reference or string constant
      WITH column_reference or string constant)
CONCAT(column_reference or string constant,
      column_reference or string constant)

In the following example, the CONCAT function is used to concatenate two strings together:

UPDATE biolife
SET notes=CONCAT(Notes WITH #13+#10+#13+#10+'End of Notes')

CONCAT can only be used with string or memo columns or constants.