Character functions

The following tables describe the supported character functions.

Character functions returning character values

Function	Description
CHR	Returns the character whose binary code equals n as a VARCHAR2 in the database character set; with USING NCHAR_CS, returns the character in the national character set.
CONCAT	Returns char1 concatenated with char2. Accepts CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The result uses the character set of char1, and its data type depends on the argument types.
INITCAP	Returns char with the first letter of each word in uppercase and all other letters in lowercase. Words are delimited by whitespace or nonalphanumeric characters.
LOWER	Returns char with all letters in lowercase. Accepts CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value has the same data type as char, and letter case is determined by the binary mapping of the character set.
LPAD	Returns expr1 left-padded to n characters with the characters from expr2. Useful for formatting query output.
LTRIM	Returns char with all leading characters in set removed. If set is omitted, a single blank is used. Scanning starts from the first character of char and stops at the first character not in set.
NCHR	Returns the character whose binary code equals number in the national character set. The return value is always NVARCHAR2. Equivalent to CHR with the USING NCHAR_CS clause.
NLS_INITCAP	Returns char with the first letter of each word in uppercase and all other letters in lowercase, using linguistic rules specified by nlsparam. Accepts CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The return value is VARCHAR2 in the same character set as char.
NLS_LOWER	Returns char with all letters in lowercase, using linguistic rules specified by nlsparam. Accepts CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is VARCHAR2 if char is a character type, or a LOB if char is a LOB, in the same character set as char.
NLS_UPPER	Returns char with all letters in uppercase, using linguistic rules specified by nlsparam. Accepts CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is VARCHAR2 if char is a character type, or a LOB if char is a LOB, in the same character set as char.
NLSSORT	Returns a collation key for char using the specified or default collation. A collation key is a byte string that ensures the binary order of keys matches the linguistic order of the source values. Accepts CHAR, VARCHAR2, NCHAR, or NVARCHAR2.
REGEXP_REPLACE	Returns source_char with every match of a regular expression pattern replaced by replace_string. Extends the functionality of REPLACE. The return value is VARCHAR2 if source_char is not a LOB, or CLOB if source_char is a LOB, in the same character set as source_char.
REGEXP_SUBSTR	Returns the substring of source_char that matches a regular expression pattern. Extends the functionality of SUBSTR and is similar to REGEXP_INSTR, but returns the substring itself instead of its position. The return value is VARCHAR2 or CLOB in the same character set as source_char.
REPLACE	Returns char with every occurrence of search_string replaced by replacement_string. If replacement_string is omitted or null, all occurrences of search_string are removed. If search_string is null, char is returned.
RPAD	Returns expr1 right-padded to n characters with expr2, repeated as needed. Useful for formatting query output.
RTRIM	Returns char with all trailing characters in set removed. If set is omitted, a single blank is used. Useful for formatting query output.
SOUNDEX	Returns a character string with the phonetic representation of char. Allows comparison of words that differ in spelling but sound alike in English.
SUBSTR	Returns a portion of char starting at position and of length substring_length. SUBSTR measures length in characters defined by the input character set. Variants differ in how they measure length: SUBSTRB uses bytes, SUBSTRC uses Unicode complete characters, SUBSTR2 uses UCS2 code points, and SUBSTR4 uses UCS4 code points.
TRANSLATE	Returns expr with each character in from_string replaced by its corresponding character in to_string. Characters in expr not found in from_string remain unchanged. If from_string is longer than to_string, extra characters are removed from the return value when they appear in expr.
TRANSLATE ... USING	Returns char converted between the database character set and the national character set, using the character set specified in the USING clause.
TRIM	Returns trim_source with leading, trailing, or both types of characters removed. If trim_character or trim_source is a character literal, it must be enclosed in single quotation marks.
UPPER	Returns char with all letters in uppercase. Accepts CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value has the same data type as char, with letter case determined by the binary mapping of the character set.

Character functions returning number values

Function	Description
ASCII	Returns the decimal code of the first character of char in the database character set.
INSTR	Returns the position of the first character of substring in string. Searches forward or backward by comparing substrings of equal length. Returns 0 if no match is found.
LENGTH	Returns the length of char. LENGTH counts characters in the input character set. Variants differ in how length is measured: LENGTHB uses bytes, LENGTHC uses Unicode complete characters, LENGTH2 uses UCS2 code points, and LENGTH4 uses UCS4 code points.
REGEXP_COUNT	Returns the number of times a regular expression pattern occurs in source_char. Evaluates strings using the input character set. Returns an integer, or 0 if no match is found.
REGEXP_INSTR	Returns the position of a substring in source_char that matches a regular expression pattern. Evaluates strings using the input character set. Returns the beginning or ending position depending on return_option, or 0 if no match is found.

Character set functions

Function	Description
NLS_CHARSET_DECL_LEN	Returns the declaration length, in characters, of an NCHAR column. Takes byte_count as the column width and char_set_id as the column’s character set ID.
NLS_CHARSET_ID	Returns the collation ID number for a given collation name. Collation IDs are used in data dictionary tables and Oracle Call Interface (OCI), while collation names are used in SQL statements and data dictionary views.
NLS_CHARSET_NAME	Returns the collation name for a given collation ID number. Collation IDs are used in data dictionary tables and Oracle Call Interface (OCI), while collation names are used in SQL statements and data dictionary views.