Archive

Posts Tagged ‘sql functions’

Oracle – SQL functions


Numeric results

A single-row function may accept multiple input parameters but always returns one value per row.

Single-row numeric functions always return numeric results. Numeric functions are the only category of functions that always return numeric results.

Character and date functions like LENGTH and MONTHS_BETWEEN return numeric results.

Character results

If the input argument is char or varchar2, then the returned value is varchar2.

For functions that returnĀ  char or varchar2, if the length of the return value exceeds the maximum length limit of the datatype, then Oracle truncates it and returns the result without an error message.

For functions that return clob values, if the length of the return value exceeds the maximum length limit of the datatype, then Oracle raises and error and no result is returned.

Character Functions Returning Number Values

Character functions that return number values can take any character datatype as their argument.

Datetime Functions

Datetime functions operate on date, timestamp, and interval.

Some of the datetime functions were designed specifically for the Oracle date datatype. If you provide a timestamp value as their argument, then Oracle internally converts this to a date value and returns a date value. However, the months_between function returns a number, and the round and trunc functions do not accept timestamp or interval at all.

Conversion Functions

Conversion functions convert a value from one datatype to another. You normally do this by datatype1 to datatype2 where datatype1 is the current datatype, and datatype2 is the new datatype.

Aggregate Functions

Distinct causes an aggregate function to consider only unique values of the argument expression.

All causes and aggregate function to consider all values, including duplicates.

All aggregate functions except count(*) and grouping ignore nulls.

You can nest aggregate functions. For example you could find the average maximum salary for an employee in the employees table.

Analytic Functions

Analytic functions compute a value based on a group of rows. They are different from aggregate functions, in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. The window determines the range of rows used to perform the calculations of the current row.

Analytic functions are the last set of operations performed in a query except for the final order by clause. Analytic functions can appear only in the select list or order by clause.

Further Reading

SQL Functions Oracle Documentation

Oracle – PL/SQL instr command


The instr command can be used in PL/SQL to return the location of a substring in a string.

Syntax:

instr( string1, string2 [, start_position [, nth_appearance ] ] )

string1 – string to search

string2 – string to find

start_position – position to start searching from

nth_appearance – appearance of string to search for

Examples:

instr(‘she_sells_sea_shells_on_the_sea_shore’,’s’,4,3) – returns 14, the 3rd appearance of s after the 4th letter.

Further Reading:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions068.htm