This topic describes the functions and operators that are used to identify and manipulate string values.
Strings include values of the CHAR, VARCHAR2, and CLOB types. Unless otherwise specified, the functions listed in the following table can work on all these types of values. However, take note of this point: If you use the CHAR string type, spaces may be automatically appended to the strings. In most cases, the following functions can also work on data of non-string types by converting the data into the values of string types.
| Function | Return type | Description | Example | Result |
|---|---|---|---|---|
| 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 |
The following table lists other available string manipulation functions. Some of the functions are used in an internal way to implement the SQL-standard string functions listed in Comparison operators.
| Function | Return type | Description | Example | Result |
|---|---|---|---|---|
| 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 |