String Functions
String functions are used to process and manipulate columns that consist of text information, such as CHAR or LONGVARCHAR data types.
The string functions support multiple-byte character strings. (Note, however, that CASE (string) does not support multiple-byte character strings. The CASE (string) keyword assumes that the string data is single-byte ASCII. See CASE (string).)
Arguments denoted as string can be the name of column, a string literal, or the result of another scalar function.
Table 55
String Functions  
Function
Description
ASCII (string)
Returns the ASCII value of the left most character of string
BIT_LENGTH (string)
Returns the length in bits of string
CHAR (code)
Returns the ASCII character corresponding to ASCII value code. The argument must be an integer value.
CHAR_LENGTH (string)
Returns the number of characters in string as defined for the column. Blanks are significant.
CHARACTER_LENGTH (string)
Same as CHAR_LENGTH.
CONCAT (string1, string2)
Returns a string that results from combining string1 and string2.
ISNUMERIC(string)
Returns 1 (TRUE) if the string value can be evaluated as a numeric value; otherwise returns 0 (FALSE).
LCASE or LOWER (string)
Converts all upper case characters in string to lower case.
LEFT (string, count)
Returns the left most count of characters in string. The value of count is an integer.
LENGTH (string)
Returns the number of characters in string. Trailing spaces are counted in a VARCHAR or LONGVARCHAR string. Trailing NULLs are counted in a CHAR and LONGVARCHAR string. The string termination character is not counted.
LOCATE (string1, string2 [, start ])
Returns the starting position of the first occurrence of string1 within string2. The search within string2 begins at the first character position unless you specify a starting position (start). The search begins at the starting position you specify. The first character position in string2 is 1. The string1 is not found, the function returns the value zero.
LTRIM (string)
Returns the characters of string with leading blanks removed.
OCTET_LENGTH (string)
Returns the length of string in octets (bytes).
POSITION (string1, string2)
Returns the position of string1 in string2. If string1 does not exist in string2, a zero is returned.
REPLACE (string1, string2, string3)
Searches string1 for occurrences of string2 and replaces each with string3. Returns the result. If no occurrences are found, string1 is returned.
REPLICATE (string, count)
Returns a character string composed of string repeated count times. The value of count is an integer.
REVERSE(string)
Returns a character string with the order of the characters reversed. Note that leading spaces in any string types are considered as significant, unlike trailing spaces which are not considered as significant. See Examples for an example.
RIGHT (string, count)
Returns the right most count of characters in string. The value of count is an integer.
RTRIM (string)
Returns the characters of string with trailing blanks removed.
SOUNDEX (string)
Converts an alpha string to a four character code to find similar sounding words or names. Returns a four character (SOUNDEX) code to evaluate the similarity of two strings, usually a name.
Note: Conforms to the current rule set for the official implementation of Soundex used by the United States Government.
SPACE (count)
Returns a character string consisting of count spaces.
STUFF (string1, start, length, string2)
Returns a character string where length characters in string1 beginning at position start have been replaced by string2. The values of start and length are integers.
SUBSTRING (string1, start, length)
Returns a character string derived from string1 beginning at the character position specified by start for length characters.
UCASE or UPPER (string)
Converts all lower case characters in string to upper case.
Queries containing a WHERE clause with scalar functions RTRIM or LEFT can be optimized. For example, consider the following query:
SELECT * FROM T1, T2 WHERE T1.C1 = LEFT(T2.C1, 2)
In this case, both sides of the predicate are optimized if T1.C1 and T2.C2 are index columns. The predicate is the complete search condition following the WHERE keyword. Depending on the size of the tables involved in the join, the optimizer chooses the appropriate table to process first.
LTRIM and RIGHT cannot be optimized if they are contained in a complex expression on either side of the predicate.
Examples
The following example creates a new table with an integer and a character column. It inserts 4 rows with values for the character column only, then updates the integer column of those rows with the ASCII character code for each character.
CREATE TABLE numchars(num INTEGER,chr CHAR(1) CASE)
INSERT INTO numchars (chr) VALUES('a')
INSERT INTO numchars (chr) VALUES('b')
INSERT INTO numchars (chr) VALUES('A')
INSERT INTO numchars (chr) VALUES('B')
UPDATE numchars SET num=ASCII(chr)
SELECT * FROM numchars
Results of SELECT:
num chr
---------- ---
97 a
98 b
65 A
66 B
 
SELECT num FROM numchars WHERE num=ASCII('a')
Results of SELECT:
num
------
97
============ 
The following example concatenates the first and last names in the Person table and results in "RooseveltBora".
SELECT CONCAT(First_name, Last_name) FROM Person WHERE First_name = 'Roosevelt'
============ 
The next example changes the case of the first name to lowercase and then to upper case, and results in "roosevelt", "ROOSEVELT".
SELECT LCASE(First_name),UCASE(First_name) FROM Person WHERE First_name = 'Roosevelt'
============ 
The following example results in first name trimmed to three characters beginning from left, the length as 9 and locate results 0. This query results in "Roo", 9, 0
SELECT LEFT(First_name, 3),LENGTH(First_name), LOCATE(First_name, 'a') FROM Person WHERE First_name = 'Roosevelt'
============ 
The following example illustrates use of LTRIM and RTRIM functions on strings, results in "Roosevelt", "Roosevelt", "elt".
SELECT LTRIM(First_name),RTRIM(First_name), RIGHT(First_name,3) FROM Person WHERE First_name = 'Roosevelt'
============ 
This substring lists up to three characters starting with the second character in the first name as “oos.”
SELECT SUBSTRING(First_name,2, 3) FROM Person WHERE First_name = 'Roosevelt'
============ 
The following example illustrates use of the SOUNDEX function on strings, "Smith" and "Smythe".
SELECT SOUNDEX (’Smith’), SOUNDEX (’Smythe’)
Results set:
S530
S530
============ 
The following example illustrates use of the SOUNDEX function on the Person table finding all last names that sound like "Kennedy".
SELECT Last_Name FROM Person WHERE SOUNDEX(last_name) = SOUNDEX ('Kennedy')
Results of SELECT:
Last_Name
---------
Kandy
Kenady
Kennedy
Kennedy
============ 
The following example illustrates use of the REVERSE function.
SELECT REVERSE(dept_name) from COURSE where dept_name = 'Music'
Results set:
               cisuM
               cisuM
               cisuM
               cisuM
               cisuM
 
5 rows were affected.
 
Because leading spaces are signficant, the following query returns zero rows:
SELECT * from COURSE WHERE REVERSE(dept_name) = 'cisuM'
This is because dept_name is defined as a CHAR field 20 characters wide. Either of the following query statements returns the expected results:
SELECT * from COURSE WHERE REVERSE(dept_name) = '               cisuM'
 
SELECT * from COURSE WHERE LTRIM(REVERSE(dept_name)) = 'cisuM'
 
Results set:
MUS 101   Hymnology      3   Music
MUS 102   Church         3   Music
MUS 203   Piano          3   Music
MUS 304   Music Theory   3   Music
MUS 405   Recital        3   Music
 
5 rows were affected.