String Functions
Chapter contents
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 5-1 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.
|
|
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.
|
|
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.
|
|
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:
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'
