Below is a list of built-in string/character functions that are available in Oracle Database.
| Function | Description |
|---|---|
ASCII() | Returns the decimal representation in the database character set of the first character of its argument. |
CHR() | Returns a character based on the code values provided as an argument. |
CONCAT() | Performs a string concatenation on its arguments. |
INITCAP() | Returns its argument with the first letter of each word in uppercase, and all other letters in lowercase. |
INSTR() | Searches for a substring in a given string, and returns an integer indicating the position of the first character of this substring. |
LENGTH() | Returns the length of its argument. There are five separate functions; LENGTH(), LENGTHB(), LENGTHC(), LENGTH2(), and LENGTH4(). Each function calculates the length in a different way. |
LOWER() | Returns its argument with all letters in lowercase. |
LPAD() | Pads the left part of a string with a certain character, to a specified number of characters. |
LTRIM() | Trims the left part of a string. By default, it trims white space, but you can optionally specify a different character or characters to trim. |
NCHR() | Returns the character having the binary equivalent to its argument in the national character set. |
NLS_INITCAP() | Similar to the INITCAP() function, except that it accepts a second argument that allows you to specify the collation. |
NLS_LOWER() | Similar to the LOWER() function, except that it accepts a second argument that allows you to specify the collation. |
NLS_UPPER() | Similar to the UPPER() function, except that it accepts a second argument that allows you to specify the collation. |
NLSSORT() | Returns a collation key for a given character value and an explicitly or implicitly specified collation. |
REGEXP_COUNT() | Returns the number of times a pattern occurs in a source string. |
REGEXP_INSTR() | Extends the functionality of the INSTR() function by allowing us to use regular expression patterns. |
REGEXP_REPLACE() | Extends the functionality of the REPLACE() function by allowing us to use regular expression patterns. |
REGEXP_SUBSTR() | Extends the functionality of the SUBSTR() function by allowing us to use regular expression patterns. |
REPLACE() | Allows you to replace a part of a string with another string. |
RPAD() | Pads the right part of a string with a certain character, to a specified number of characters. |
RTRIM() | Trims the right part of a string. By default, it trims white space, but you can optionally specify a different character or characters to trim. |
SOUNDEX() | Returns a character string containing the phonetic representation of its argument. |
SUBSTR() | Returns the specified substring from a given string. |
TRANSLATE() | Allows you to make several single-character, one-to-one substitutions in one operation. |
TRANSLATE(...USING) | Converts its argument into the character set specified for conversions between the database character set and the national character set. Not to be confused with the TRANSLATE() function, which allows you to make several single-character, one-to-one substitutions in one operation. |
TRIM() | Allows you to trim characters from the sides of a string. You can trim leading characters, trailing characters, or both. |
UPPER() | Returns its argument with all letters in uppercase. |