在这个章节中介绍了用于检测和操作字符串值的函数和操作符。

字符串包括CHAR、VARCHAR2和CLOB类型的值。除非另外提示，在下面所列出的函数可以在所有这些类型的值上工作，但是需要注意的是当使用CHAR类型的字符串时，会出现自动在字符串后面添加空格的情况。一般来说，通过将数据转换成字符串类型的值，在这里介绍的函数也可以在非字符串类型的值上工作。

函数返回类型描述示例结果
函数 返回类型 描述 示例 结果
string || string CLOB String concatenation 'Polar' || 'DB' PolarDB
CONCAT(string, string) CLOB String concatenation CONCAT('a' || 'b') ab
HEXTORAW(varchar2) RAW Converts a VARCHAR2 value to a RAW value HEXTORAW('303132') '012'
RAWTOHEX(raw) VARCHAR2 Converts a RAW value to a HEXADECIMAL value RAWTOHEX('012') '303132'
INSTR(string, set, [ start [, occurrence ] ]) INTEGER Finds the location of a set of characters in a string, starting at position start in the string, string, and looking for the first, second, third and so on occurrences of the set. Returns 0 if the set is not found. INSTR('PETER PIPER PICKED a PECK of PICKLED PEPPERS','PI',1,3) 30
INSTRB(string, set, start) INTEGER Returns the position of the set within the string, beginning at start. Returns 0 if set is not found. INSTRB('PETER PIPER PICKED a PECK of PICKLED PEPPERS','PICK', 14) 30
INSTRB(string, set, start, occurrence) INTEGER Returns the position of the specified occurrence of set within the string, beginning at start. Returns 0 if set is not found. INSTRB('PETER PIPER PICKED a PECK of PICKLED PEPPERS','PICK', 1, 2) 30
LOWER(string) CLOB Convert string to lower case LOWER('TOM') tom
SUBSTR(string, start [, count ]) CLOB Extract substring starting from start and going for count characters. If count is not specified, the string is clipped from the start till the end. SUBSTR('This is a test',6,2) is
SUBSTRB(string, start [, count ]) CLOB Same as SUBSTR except start and count are in number of bytes. SUBSTRB('abc',3) (assuming a double-byte character set) c
SUBSTR2(string, start[, count ]) CLOB Alias for SUBSTR. SUBSTR2('This is atest',6,2) is
SUBSTR2(string, start [, count ]) CLOB Alias for SUBSTRB. SUBSTR2('abc',3) (assuming a double-byte character set) c
SUBSTR4(string, start [, count ]) CLOB Alias for SUBSTR. SUBSTR4('This is a test',6,2) is
SUBSTR4(string, start [, count ]) CLOB Alias for SUBSTRB. SUBSTR4('abc',3) (assuming a double-byte character set) c
SUBSTRC(string, start [, count ]) CLOB Alias for SUBSTR. SUBSTRC('This is a test',6,2) is
SUBSTRC(string, start [, count ]) CLOB Alias for SUBSTRB. SUBSTRC('abc',3) (assuming a double-byte character set) c
TRIM([ LEADING | TRAILING | BOTH ] [ characters ] FROM string) CLOB Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string. TRIM(BOTH 'x' FROM 'xTomxx') Tom
LTRIM(string [, set]) CLOB Removes all the characters specified in set from the left of a given string. If set is not specified, a blank space is used as default. LTRIM('abcdefghi', 'abc') defghi
RTRIM(string [, set]) CLOB Removes all the characters specified in set from the right of a given string. If set is not specified, a blank space is used as default. RTRIM('abcdefghi', 'ghi') abcdef
UPPER(string) CLOB Convert string to upper case UPPER('tom') TOM

在下面的表格中列出了另外一些允许使用的字符串操作函数。它们中的一些是用于内部实现在比较运算符表中列出，符合SQL标准的字符串函数。

函数返回类型描述示例结果
函数 返回类型 描述 示例 结果
ASCII(string) INTEGER ASCII code of the first byte of the argument ASCII('x') 120
CHR(INTEGER) CLOB Character with the given ASCII code CHR(65) A
DECODE(expr, exprla, exprlb [, expr2a, expr2b ]... [, default ]) Same as argument types of expr1b, expr2b,..., default Finds first match of expr with expr1a, expr2a, etc. When match found, returns corresponding parameter pair, expr1b, expr2b, etc. If no match found, returns default. If no match found and default not specified, returns null. DECODE(3, 1,'One', 2,'Two', 3,'Three', 'Not found') Three
INITCAP(string) CLOB Convert the first letter of each word to uppercase and the rest to lowercase. Words are sequences of alphanumeric characters separated by non- alphanumeric characters. INITCAP('hi THOMAS') Hi Thomas
LENGTH INTEGER Returns the number of characters in a string value. LENGTH('Coted''Azur') 11
LENGTHC INTEGER This function is identical in functionality to LENGTH; the function name is supported for compatibility. LENGTHC ('Cote d''Azur') 11
LENGTH2 INTEGER This function is identical in functionality to LENGTH; the function name is supported for compatibility. LENGTH2 ('Cote d''Azur') 11
LENGTH4 INTEGER This function is identical in functionality to LENGTH; the function name is supported for compatibility. LENGTH4 ('Cote d''Azur') 11
LENGTHB INTEGER Returns the number of bytes required to hold the given value. LENGTHB ('Cote d''Azur') 12
LPAD(string, length INTEGER [, fill ]) CLOB Fill up string to size, length by prepending the characters, fill (a space by default). If string is already longer than length then it is truncated (on the right). LPAD('hi', 5, 'xy') xyxhi
REPLACE(string, search string [, replace string ] CLOB Replaces one value in a string with another. If you do not specify a value for replace string, the search_string value when found, is removed. REPLACE( 'GEORGE', 'GE', 'EG') EGOREG
RPAD(string, length INTEGER [, fill ]) CLOB Fill up string to size, length by appending the characters, fill (a space by default). If string is already longer than length then it is truncated. RPAD('hi', 5, 'xy') hixyx
TRANSLATE(string, from, to) CLOB Any character in string that matches a character in the from set is replaced by the corresponding character in the to set. TRANSLATE('12345', '14', 'ax') a23x5