List of SQL String Functions
ASCII(string_exp)
Returns the ASCII code value of the leftmost character of string_exp as an integer.
Example: SELECT ASCII('CONNX') returns 67 (ASCII code for 'C').
BIT_LENGTH(string_exp)
Returns the length in bits of the string expression.
Example: SELECT BIT_LENGTH('ABCDEFG') returns 56 (length in bits of a 7-character string expression).
CHAR_LENGTH(string_exp)
Returns the length in characters of the string expression if the string expression is of a Character data type; otherwise, returns the length in bytes of the string expression.
Example: SELECT CHAR_LENGTH('ABCDEFG') returns 7 (number of characters in string expression).
CHARACTER_LENGTH(string_exp)
Returns the length in characters of the string expression if the string expression is of a Character data type; otherwise, returns the length in bytes of the string expression.
Example: SELECT CHARACTER_LENGTH('ABCDEFG') returns 7 (length of string expression).
CHR(code)
Returns the character that has the ASCII code value specified by code. The value of code should be between 0 and 255.
Example: SELECT CHR(100) returns d (ASCII code for '100').
CONCAT(string_exp1, string_exp2)
Returns a character string that is the result of concatenating string_exp2 to string_exp1. The || operator may also be used to concatenate two values.
Example: SELECT CONCAT('ABCDEFG','HIJKLMN') returns ABCDEFGHIJKLMN.
SELECT 'ABCDEFG' || 'HIJKLMN' returns ABCDEFGHIJKLMN.
DIFFERENCE(string_exp1, string_exp2)
Returns an integer value that indicates the difference between the values returned by the SOUNDEX function for string_exp1 and string_exp2.
Example: SELECT DIFFERENCE('ABC','DEF') returns -2026 (the difference between the values returned by the SOUNDEX function).
HEX(numeric_expr)
Returns the hexadecimal representation of the numeric value in character form.
Example: SELECT HEX(15) returns F (hexadecimal value of 15).
INSERT(string_exp1, 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_exp, beginning at start.
Example: SELECT INSERT('ABCDEFGHIJ',3,4,'MMMMM') returns ABMMMMMGHIJ.
LCASE(string_exp)
Returns a string equal to that in string_exp with all uppercase characters converted to lowercase.
Example: SELECT LCASE('ABCDEFG') returns abcdefg.
LEFT(string_exp, count_exp)
Returns the leftmost count characters of string_exp.
Example: SELECT LEFT('ABCDEFG',4) returns ABCD.
LENGTH(string_exp)
Returns the number of characters in string_exp, excluding trailing blanks.
Example: SELECT LENGTH('ABCEDFG') returns 7.
LOCATE(string_exp1, string_exp2[, start])
Returns the starting position (one based) of the first occurrence of string_exp2 within string_exp1. The search for the first occurrence of string_exp2 begins with the first character position in string_exp1 starting at the specified position. If the start position is omitted, then the search is performed from the beginning of the string.
Example: SELECT LOCATE('ABCDEFG','DEFG') returns 4.
LTRIM(string_exp)
Returns the characters of string_exp, with leading blanks removed.
Example: SELECT LTRIM(' ABCDEFG') returns ABCDEFG.
LTRIMWHITESPACE(string_exp)
Returns the characters of string_exp, with leading white space characters (space, tab, new line, carriage return) removed.
Example: SELECT LTRIMWHITESPACE(' ABCDEFG') returns ABCDEFG.
OCTET_LENGTH(string_exp)
Returns the length in bytes of the string expression. The result is the smallest integer not less than the number of bits divided by 8.
Example: SELECT OCTET_LENGTH('ABCDEFG') returns 7.
POSITION(string_exp IN string_exp)
Returns the starting position (one based) of the first character expression in the second character expression.
Example: SELECT POSITION('CDE' IN 'ABCDEFG') returns 3.
REMOVENULL(string_exp)
Replaces all embedded binary zeros in a string with spaces.
Example: removenull('abcde'+chr(0)+'moretext') returns 'abcde moretext' - where the binary zero is replaced by a space.
REPEAT(string_exp, count)
Returns a character string composed of string_exp repeated count times.
Example: SELECT REPEAT('ABC',3) returns ABCABCABC.
REPLACE(string_exp1, string_exp2, string_exp3)
Search string_exp1 for occurrences of string_exp2 and replace with string_exp3.
Example: SELECT REPLACE('ABCDE','CDE','FG') returns ABFG.
RIGHT(string_exp, count)
Returns the rightmost count characters of string_exp.
Example: SELECT RIGHT('ABCDE',2) returns DE.
RTRIM(string_exp)
Returns the characters of string_exp with trailing blanks removed.
Example: SELECT RTRIM('ABCDE ') returns ABCDE.
RTRIMWHITESPACE(string_exp)
Returns the characters of string_exp with trailing white space characters (space, tab, new line, carriage return) removed.
Example: SELECT RTRIMWHITESPACE('ABCDE ') returns ABCDE.
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.
Example: SELECT Substring('ABCDE', 2, 1) returns 'B'.
Example: SELECT Substring('ABCDE', 4, 2) returns 'DE'.
SOUNDEX(string_exp)
Returns a data source-dependent character string representing the sound of the words in string_exp. A SOUNDEX code is returned.
Example: SELECT SOUNDEX('SMITH') returns S53 (the SOUNDEX code for words that sound like "Smith").
SPACE(count)
Returns a character string consisting of count spaces.
Example: SELECT SPACE(12) returns a character string of 12 count spaces.
TRIM(string_exp)
Returns the characters of string_exp with both leading and trailing blanks removed.
Example: SELECT TRIM(' ABCDE ') returns ABCDE.
TRIMWHITESPACE(string_exp)
Returns the characters of string_exp with both leading and trailing white space characters (space, tab, new line, carriage return) removed.
Example: SELECT TRIMWHITESPACE(' ABCDE ') returns ABCDE.
UCASE(string_exp)
Returns a string equal to that in string_exp with all lowercase characters converted to uppercase.
Example: SELECT UCASE('abcde') returns ABCDE.