|
Function
|
Description
|
|
ASCII(string_exp)
|
Returns the ASCII code value of the leftmost character of string_exp as an integer. For example:
SELECT ASCII(ename) FROM emp WHERE ename = 'Adam';
This returns 65, which is the ASCII code of A.
|
|
BIT_LENGTH(string_exp)
|
Returns the length in bits of the string expression. For example:
SELECT BIT_LENGTH(ename) FROM emp WHERE ename = 'John';
This returns 32, which is the number of bits.
|
|
CHAR(code)
|
Returns the character that has the ASCII code value specified by code. The value of code should be between 0 and 255; otherwise, the return value is data source–dependent. For example:
SELECT CHAR(65) FROM emp;
This returns A, which is the character for ASCII code A.
|
|
CHAR_LENGTH(string_exp)
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 (the smallest integer not less than the number of bits divided by 8). (This function is the same as the CHARACTER_LENGTH function.) For example:
SELECT CHAR_LENGTH(ename) FROM emp where ename = 'John';
This returns 4.
|
|
CONCAT(string_exp1, string_exp2)
|
Returns a character string that is the result of concatenating string_exp2 to string_exp1. If either of string_exp1 or string_exp2 is a NULL value, it returns a NON-NULL string.
If either of string_exp1 or string_exp2 is wide character string, the return value is a wide character string. For example:
SELECT CONCAT('Name is: ', ename) FROM emp WHERE ename = 'John';
This returns 'Name is: John'
SELECT CONCAT(N'Name is: ', ename) FROM emp WHERE ename = N'John';
This returns wide character string N'Name is: John'.
For more information on nullability, refer to Nullability.
|
|
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.
If string_exp1 is wide character string, the return value is a wide character string. Offsets (start and length) must be specified in number of characters. For example:
SELECT INSERT(ename, 1, 0, 'Name is: ') FROM emp WHERE ename = 'John';
This returns 'Name is: John'
SELECT INSERT(ename, 1, 0, N'Name is: ') FROM emp WHERE ename = N'John';
If ename is a column of wide character data type, this returns wide character string: N'Name is: John'.
|
|
LCASE(string_exp)
LOWER(string_exp)
|
Returns a string equal to that in string_exp, with all uppercase characters converted to lowercase. For example:
SELECT LCASE(ename) FROM emp WHERE ename is 'John';
This returns 'john'.
|
|
LEFT(string_exp, count)
|
Returns the leftmost count characters of string_exp.
If string_exp is wide character string, the return value is a wide character string. Offset (count) must be specified in number of characters. For example:
SELECT LEFT(ename, 2) FROM emp WHERE ename = 'John';
This returns 'jo'.
SELECT LEFT(ename, 2) FROM emp WHERE ename = N'John';
If ename is a column of wide character data type, this returns wide character string N'jo'.
|
|
LENGTH(string_exp)
|
Returns the number of characters in string_exp, excluding trailing blanks.
If string_exp is wide character string, the return value is a number of wide characters in string_exp. Trailing blanks are not checked in wide character implementation. For example:
SELECT LENGTH('John '), LENGTH('John') FROM emp;
This returns 4 for both result columns as trailing blanks are excluded.
SELECT LENGTH(N'John '), LENGTH(N'John') FROM emp;
This returns 7 for the first result column and 4 for the second result column. Trailing blanks are not checked in wide character implementation.
|
|
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 argument, start, is specified. If start is specified, the search begins with the character position indicated by the value of start. The first character position in string_exp2 is indicated by the value 1. If string_exp1 is not found within string_exp2, the value 0 is returned.
If string_exp2 is a wide character string, returns the starting position of the first occurrence of string_exp1 within the wide character string string_exp2. Offset (start) must be specified in number of characters. If string _exp2 is a wide character exp, the result is computed by treating both arguments as wide character string. For example:
SELECT LOCATE('h', 'John', 1) FROM emp;
This returns 3 as 'h' is found at the third position.
SELECT LOCATE(N'h', N'John', 1) FROM emp;
This returns 3 as N'h' is found at the third position.
|
|
LTRIM(string_exp)
|
Returns the characters of string_exp, with leading blanks removed. For example:
SELECT LTRIM(' ABC') FROM emp;
This returns 'ABC'.
|
|
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. For example:
SELECT OCTET_LENGTH(ename) FROM emp WHERE ename = 'John';
This returns 4.
|
|
POSITION(character_exp1 character_exp2)
|
Returns the position of the first character expression in the second character expression. The result is an exact numeric with an implementation-defined precision and a scale of 0.
If character_exp1 and character_exp2 are wide character strings, returns the position of the first wide character expression in the second wide character expression. If character_exp2 is a wide character string, the result is computed by treating both arguments as wide character strings. For example:
SELECT POSITION('abc', '1234abc def') FROM emp;
This returns 5.
SELECT POSITION(N'abc', N'1234abc def') FROM emp;
This returns 5.
|
|
REPEAT(string_exp, count)
|
Returns a character string composed of string_exp repeated count times.
If string_exp is wide character string, the return value is a wide character string. For example:
SELECT REPEAT(ename, 2) FROM emp WHERE ename = 'John';
This returns 'JohnJohn'
SELECT REPEAT(ename, 2) FROM emp WHERE ename = N'John';
If ename is a column of wide character data type, this returns N'JohnJohn'.
|
|
REPLACE(string_exp1, string_exp2, string_exp3)
|
Search string_exp1 for occurrences of string_exp2, and replace with string_exp3.
If string_exp1 is wide character string, the return value is a wide character string. For example:
SELECT REPLACE(address, 'San Francisco', 'SFO') FROM emp where address = '100 Vanness, San Francisco';
This returns '100 Vanness, SFO'.
SELECT REPLACE(address, N'San Francisco', N'SFO') FROM emp WHERE address = N'100 Vanness, San Francisco';
If address is a column of wide character data type, this returns N'100 Vanness, SFO'.
|
|
RIGHT(string_exp, count)
|
Returns the right-most count characters of string_exp.
If string_exp is wide character string, the return value is a wide character string. Offset (count) must be specified in number of characters. For example:
SELECT RIGHT(ename, 2) FROM emp WHERE ename = 'John';
This returns 'hn'.
SELECT RIGHT(ename, 2) FROM emp WHERE ename = N'John';
If ename is a column of wide character data type, this returns N’hn'.
|
|
RTRIM(string_exp)
|
Returns the characters of string_exp with trailing blanks removed. For example:
SELECT RTRIM('abc ') FROM emp;
This returns 'abc'.
|
|
SPACE(count)
|
Returns a character string consisting of count spaces. For example:
SELECT ename+space(5)+ename FROM emp WHERE ename = 'John';
This returns 'John John'.
|
|
SUBSTRING(string_exp, start, length)
SUBSTR(string_exp, length)
|
Returns a character string that is derived from string_exp, beginning at the character position specified by start for length characters.
If string_exp is wide character string, the return value is a wide character string. Offset (start and length) must be specified in number of characters. For example:
SELECT SUBSTR(ename, 1, 3) FROM emp WHERE ename ='John';
This returns 'Joh'
SELECT SUBSTR(ename, 1, 3) FROM emp WHERE ename =N’John';
If ename is a column of wide character data type, this returns N’Joh'
|
|
UCASE(string_exp)
UPPER(string_exp)
|
Returns a string equal to that in string_exp, with all lowercase characters converted to uppercase. For example:
SELECT UCASE(ename) FROM emp WHERE ename = 'John';
This returns 'JOHN'.
|