w3resource logo


Oracle Tutorial

Oracle Character Functions

Secondary Nav

Introduction

The Oracle Character functions that take the input argument is CHAR or VARCHAR2 or NCHAR or NVARCHAR2 and the value return is VARCHAR2 or NVARCHAR2 type.

Oracle Character Functions are :

Name Description
CHR The CHR function is used to return the character having the binary equivalent to n as a VARCHAR2 value in either the database character set or, from the NCHAR_CS.
CONCAT The Oracle CONCAT() function returns the result (a string) of concatenating two string values. This function is equivalent to the concatenation operator (||).
INITCAP The Oracle INITCAP() function sets the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.
LOWER The Oracle LOWER() function returns a specified character expression in lowercase letters.
LPAD The Oracle LPAD() function is used to pad the left side of a string with a specific set of characters. The function is useful for formatting the output of a query.
LTRIM The Oracle LTRIM() function is used to remove all specified characters from the left end side of a string. Optionally you can specify an initial character or characters to trim to, or it will default to a blank.
NCHR The Oracle NCHAR() function is used to get the character having the binary equivalent to the number in the national character set. This function is equivalent to using the CHR function with the USING NCHAR_CS clause.
NLS_INITCAP The Oracle NLS_INITCAP() function sets the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.
NLS_LOWER The Oracle NLS_LOWER() function returns a specified character expression in lowercase letters.
NLS_UPPER The NLS_UPPER function is used to return all letters of a given string in uppercase.
NLSSORT The NLSSORT function is used to replace a character string with the equivalent sort string used by the linguistic sort mechanism.
REGEXP_REPLACE The REGEXP_REPLACE function is used to return source_char with every occurrence of the regular expression pattern replaced with replace_string.
REGEXP_SUBSTR The REGEXP_SUBSTR function use used to return the substring that matches a regular expression within a string.
REPLACE The REPLACE function is used to return char with every occurrence of search_string replaced with replacement_string.
RPAD The Oracle RPAD function is used to pad the right side of a string with a specific set of characters. This function is useful for formatting the output of a query.
RTRIM The Oracle RTRIM function is used to remove spaces( if no character(s) is provided as trimming_text ) or set of characters which are matching with the trimming_text, from the end of a string.
SOUNDEX The Oracle SOUNDEX function returns a character string containing the phonetic representation of char. This function lets you compare words that are spelled differently, but sound alike in English.
SUBSTR The SUBSTR function returns the specified number (substring_length) of characters from a particular position of a given string. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters. SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.
TRANSLATE The Oracle TRANSLATE function returns a string with all occurrences of each character specified in another string as 2nd argument replaced by its corresponding character specified in the 3rd argument.
TRANSLATE_USING The Oracle TRANSLATE ... USING converts char into the character set specified for conversions between the database character set and the national character set.
TRIM The Oracle TRIM function is used to remove all leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, then it is necessary to enclose it in single quotation marks.
UPPER The Oracle UPPER() function returns a specified character expression in UPPERCASE letters.
ASCII The ASCII() function returns the decimal representation of the first character of a character expression.
INSTR The Oracle INSTR function is used to search string for substring and find the the location of the substring in the string. If a substring that is equal to substring is found, then the function returns an integer indicating the position of the first character of this substring. If no such substring is found, then the function returns zero.
LENGTH The Oracle LENGTH function is used to return the length of a given string. If the string has data type CHAR, then the length includes all trailing blanks. If the string is null, then this function returns null.
REGEXP_COUNT The Oracle REGEXP_COUNT function is used to count the number of times that a pattern occurs in a string. It returns an integer indicating the number of occurrences of the pattern. If no match is found, then the function returns 0.
REGEXP_INSTR The Oracle REGEXP_INSTR function is used to return the location of a regular expression pattern in a string. This function allows you to find a substring in a string using regular expression pattern matching. If no match is found, then the function returns 0.
NLS_CHARSET_DECL_LEN The Oracle NLS_CHARSET_DECL_LEN function is used to returns the declaration length (in a number of characters) of an NCHAR column.
NLS_CHARSET_ID The Oracle NLS_CHARSET_ID function is used to return the character set ID number corresponding to character set name string.
NLS_CHARSET_NAME The Oracle NLS_CHARSET_NAME is used to return the name of the character set corresponding to ID number number1. When the number is not recognized as a valid character set ID, then this function returns null


Join our Question Answer community to learn and share your programming knowledge.

Solve these problems:

Java: How to convert a string to an integer in Java?

C#: Loops in c#

SQL: JOIN using more than 5 tables