Category: Oracle

SOUNDEX() Function in Oracle

Posted on by Ian

In Oracle, the SOUNDEX() function returns a character string containing the phonetic representation of its argument. This is known as the Soundex string.

Soundex is a phonetic algorithm for indexing names by sound, as pronounced in English. If two words sound the same, they should have the same Soundex string. If two words sound similar, but not exactly the same, their Soundex string might look similar but not exactly the same.

The function accepts one argument: the string for which to return the Soundex string from.

Continue reading →

SUBSTR() Function in Oracle

Posted on by Ian

In Oracle, the SUBSTR() function returns a substring from a given string.

SUBSTR() requires at least two arguments; the string, and the position for which to extract the substring from. It also accepts an optional third argument that allows you to specify how long the substring should be.

SUBSTR() can also be thought of as a group of functions. There are five separate functions; SUBSTR(), SUBSTRB(), SUBSTRC(), SUBSTR2(), and SUBSTR4(). Each function calculates the length in a different way.

Continue reading →

NANVL() Function in Oracle

Posted on by Ian

In Oracle, the NANVL() function allows us to deal with NaN values by specifying another number to return in its place.

The way it works is, it accepts two arguments. If the first argument is NaN (not a number), it returns the second argument. If the first argument is a number, then it simply returns that number.

Note that this function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE.

Continue reading →