ASCII函数
返回与指定的字符对应的十进制数。
select ascii(‘A’), ascii(‘a’) from dual;

ASCII(‘A’) ASCII(‘A’)
———- ———-
65 97

CHR函数
给出整数,返回对应的字符。与ASCII函数互为逆运算。
select chr(65), chr(97) from dual;

CHR(65) CHR(97)
——- ——-
A a

LENGTH函数
返回字符串的长度。
select length(‘aaa’) from dual;

LENGTH(‘AAA’)
————-
3

select length(‘大家好’) from dual;

LENGTH(‘大家好’)
—————-
3

LENGTHB(c1)函数
与LENGTH()一样，按字节返回。
SELECT LENGTHB(‘AAA’) FROM dual;
LENGTHB(‘AAA’)
————–
3

SELECT LENGTHB(‘大家好’) FROM dual;
LENGTHB(‘大家好’)
——————-
6

CONCAT函数
连接两个字符串。与||操作符效果相同。

select concat(‘aaa’,’bbb’) from dual;

CONCAT(‘AAA’,’BBB’)
—————————————————————-
aaabbb

select ‘aaa’ || ‘bbb’ from dual;

‘AAA’||’BBB’
—————————————————————-
aaabbb

LOWER函数
返回字符串,并将所有的字符小写。
select lower(‘AaBbCcDd’)AaBbCcDd from dual;

AABBCCDD
——–
aabbccdd

UPPER函数
返回字符串,并将所有的字符大写。
select upper(‘AaBbCcDd’) upper from dual;

UPPER
——–
AABBCCDD

LTRIM(c1,c2)
把c1中最左边的字符去掉，使其第一个字符不在c2中，如果没有c2，那么c1就不会改变。
select LTRIM(‘aaabbbcccdddeeefffggg’, ‘abc’) “text” from dual;

text
————
dddeeefffggg

RTRIM(c1,c2)
把c1中最右边的字符去掉，使其第后一个字符不在c2中，如果没有c2，那么c1就不会改变。
select RTRIM(‘aaabbbcccdddeeefffggg’, ‘efg’) “text” from dual;

text
————
aaabbbcccddd

TRIM(c1[ from c2])
TRIM enables you to trim leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, then you must enclose it in single quotes.
If you specify LEADING, then Oracle removes any leading characters equal to trim_character.
If you specify TRAILING, then Oracle removes any trailing characters equal to trim_character.
If you specify BOTH or none of the three, then Oracle removes leading and trailing characters equal to trim_character.
If you do not specify trim_character, then the default value is a blank space.
If you specify only trim_source, then Oracle removes leading and trailing blank spaces.
The function returns a value with datatype VARCHAR2. The maximum length of the value is the length of trim_source.
If either trim_source or trim_character is null, then the TRIM function returns null.
Both trim_character and trim_source can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as trim_source.

Examples
This example trims leading and trailing zeroes from a number:

SELECT TRIM (0 FROM 0009872348900) “TRIM Example” FROM DUAL;

TRIM Example
————
98723489

TRANSLATE(c1,c2,c3)
将c1中与c2相同的字符以c3代替。
select translate(‘aaabbbcccdddeeefffggg’,’abc’,’123′) text from dual;

TEXT
———————
111222333dddeeefffggg

REPLACE(c1,c2,c3)
c1,c2,c3都是字符串，函数用c3代替出现在c1中的c2后返回。
select REPLACE(‘uptown’,’up’,’down’) from dualREPLACEdowntown

SUBSTR(c1,i,j)
c1为一字符串，i,j为整数，从c1的第i位开始返回长度为j的子字符串，如果j为空，则直到串的尾部。
The “substring” functions return a portion of string, beginning at character position, substring_length characters long.

SUBSTR calculates lengths using characters as defined by the input character set.
SUBSTRB uses bytes instead of characters.
SUBSTRC uses Unicode complete characters.
SUBSTR2 uses UCS2 codepoints. SUBSTR4 uses UCS4 codepoints.

If position is 0, then it is treated as 1.
If position is positive, then Oracle counts from the beginning of string to find the first character.
If positionis negative, then Oracle counts backward from the end of string.
If substring_length is omitted, then Oracle returns all characters to the end of string. If substring_length is less than 1, then a null is returned.

string can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB,or NCLOB. The return value is the same datatype as string.
Floating-point numbers passed as arguments to SUBSTR are automatically converted to integers.

Examples
The following example returns several specified substrings of “ABCDEFG”:
SELECT SUBSTR(’ABCDEFG’,3,4) “Substring” FROM DUAL;
Substring
———
CDEF

SELECT SUBSTR(’ABCDEFG’,-5,4) “Substring” FROM DUAL;
Substring
———
CDEF

Assume a double-byte database character set:
SELECT SUBSTRB(’ABCDEFG’,5,4.2) “Substring with bytes” FROM DUAL;
Substring with bytes
——————–
CD

select SUBSTR(‘Message’,1,4) from dual;
SUBS
Mess

STBSTR(c1,i,j)
与SUBSTR大致相同，只是I,J是以字节计算。