Below is a list of built-in string/character functions that are available in Oracle Database.
Continue readingTag: string functions
TRANSLATE(… USING) Function in Oracle
In Oracle, the TRANSLATE(...USING) function converts its argument into the character set specified for conversions between the database character set and the national character set.
This function is not to be confused with the TRANSLATE() function, which allows you to make several single-character, one-to-one substitutions in one operation.
The TRANSLATE(...USING) function is supported in Oracle primarily for ANSI compatibility. Oracle recommends that we use the TO_CHAR() and TO_NCHAR() functions instead.
LENGTH() Function in Oracle
In Oracle, the LENGTH() function returns the length of its argument.
LENGTH() can also be thought of as a group of functions. There are five separate functions; LENGTH(), LENGTHB(), LENGTHC(), LENGTH2(), and LENGTH4(). Each function calculates the length in a different way.
INSTR() Function in Oracle
In Oracle, the INSTR() function searches for a substring in a given string, and returns an integer indicating the position of the first character of this substring. If the substring isn’t found, the function returns 0.
INSTR() requires at least two arguments; the string, and the substring. It also accepts an optional third and fourth arguments that allows you to specify the starting position to search, and which occurrence to search for.
INSTR() can also be thought of as a group of functions. There are five separate functions; INSTR(), INSTRB(), INSTRC(), INSTR2(), and INSTR4(). Each function calculates the length in a different way.
ASCII() Function in Oracle
In Oracle Database, the ASCII() function returns the decimal representation in the database character set of the first character of its argument.
REGEXP_COUNT() Function in Oracle
In Oracle, the REGEXP_COUNT() function returns the number of times a pattern occurs in a source string.
REGEXP_SUBSTR() Function in Oracle
In Oracle, the REGEXP_SUBSTR() function returns a substring from a string, based on a regular expression pattern.
It extends the functionality of the SUBSTR() function by allowing us to use regular expression patterns.
REGEXP_REPLACE() Function in Oracle
In Oracle, the REGEXP_REPLACE() function replaces occurrences of the substring within a string that matches the given regular expression pattern.
It extends the functionality of the REPLACE() function by allowing us to use regular expression patterns.
REGEXP_INSTR() Function in Oracle
In Oracle, the REGEXP_INSTR() function searches a string for a regular expression pattern. It returns an integer indicating the beginning or ending position of the matched substring (whichever one you specify).
It extends the functionality of the INSTR() function by allowing us to use regular expression patterns.
NLS_LOWER() Function in Oracle
In Oracle, the NLS_LOWER() function returns its argument with all letters in lowercase.
It’s similar to the LOWER() function, except that it accepts a second argument that allows you to specify the collation. The collation handles special linguistic requirements for case conversions.