Commonly Used Functions

left(srcStr, count)

Returns a new string that is a substring of srcStr containing count of characters taken from the start of the srcStr.

right(srcStr, count)

Returns a new string that is a substring of srcStr containing count of characters taken from the end of the srcStr.

substr(srcStr, beginIndex), substr(srcStr, beginIndex, strLen)

Returns a new string that is a substring of the string srcStr.

transliterate(srcStr, charsFrom, charsTo)

Transforms characters of the string srcStr.

containsWord(srcStr, srcWord)

Searches for the occurrence of the word srcWord in the string srcStr.

indexOf(srcStr, subStr), indexOf(srcStr, subStr, fromIndex)

Returns the index within the string srcStr of the first occurrence of the specified substring subStr.

lower(srcStr)

Transforms all characters of the string srcStr to lower case.

upper(srcStr)

Transforms all characters of the string srcStr to upper case.

capitalize(srcStr)

Transforms the first character of each word to upper case and all following characters to lower case.

replicate(srcStr, n)

Returns n copies of the string srcStr concatenated together without any separator.

replace(srcStr, what, withWhat)

Replaces occurrences of the string what with the string withWhat in the string srcStr.

find(srcRegex, srcStr [, caseInsensitive])

Verifies whether the string srcStr or its parts match the regular expression srcRegex.

matches(srcRegex, srcStr [, caseInsensitive])

Verifies whether the string srcStr matches exactly the pattern of the regular expression srcRegex.

substituteAll(srcPattern, srcReplacement, srcStr [, caseInsensitiveFlag])

Replaces all occurrences of srcPattern in string srcStr with srcReplacement.

substituteMany(srcPattern, srcReplacement, srcStr, srcVolume [, caseInsensitiveFlag])

Replaces all occurrences of srcPattern in the string srcStr with srcReplacement, the maximum number of replacements is defined by the parameter srcVolume.

toDate(expr, dateFormat[, dateLocale])

Returns the date specified in expression expr converted to date type DAY.

toString(expr, strFormat[, strLocale])

Converts the expression expr to a STRING value.

datePart(srcDate, fieldName)

This function returns the value of the field fieldName (YEAR, MONTH, or DAY) of the srcDate.

dateDiff(startDate, endDate, fieldName)

Returns the difference between the endDate and startDate expressed in fieldName units.

today()

Returns the current date in type DAY.

dateAdd(srcDate, srcValue, fieldName)

Adds the specified srcValue of the type specified by fieldName (YEAR, MONTH, or DAY) to the srcDate.

hamming(srcStr1, srcStr2 [, caseInsensitive])

Returns the Hamming distance between the strings srcStr1 and srcStr2.

levenshtein(srcStr1, srcStr2 [, maxDistance][, caseInsensitive])

Returns the Levenshtein distance between the strings srcStr1 and srcStr2.

editDistance(srcStr1, srcStr2 [, maxDistance] [, caseInsensitive])

Returns the edit distance between the strings srcStr1 and srcStr2.

soundex(srcStr)

Returns the soundex value of the srcStr parameter.

doubleMetaphone(srcStr), doubleMetaphone(srcStr, isAlternate)

Encodes srcStr to a double metaphone primary string. Encodes srcStr to a double metaphone secondary string if the parameter isAlternate is true.

intersection(set1, set2, separator [, multiset])

Returns the cardinality of the intersection of sets.

difference(set1, set2, separator [, multiset] [, singularity])

Returns the cardinality of the difference of sets (set1, set2).

symmetricDifference(set1, set2, separator [, multiset ] [, singularity])

Returns the cardinality of the symmetric difference of sets.

union(set1, set2, separator [, multiset])

Returns the cardinality of the union of sets.

iif(ifExpr, trueExpr, elseExpr)

Returns trueExpr if ifExpr is true.

case(expr, exprValue[, expr, exprValue]…​[, defaultExpr])

Returns the value of the expression exprValue immediately following the first expression expr whose value is true.

decode(decodeExpr, expr, exprValue[, expr , exprValue]…​[, defaultExpr])

Returns the value of the expression exprValue immediately following the first expression expr whose value is equal to decodeExpr.

nvl(expr[, expr]…​)

Returns the value of the first expression expr whose value is not NULL.