在这个章节中介绍了用于检测和操作字符串值的函数和操作符。
字符串包括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 |