SQL Plus Functions in Oracle (single row functions)

[.BZU.]

SQL Plus Functions in Oracle

There are two types of functions.

• Multiple Row Functions:
• Single row functions: these function operate on single row and reteun one result per row.e.g. characters, numbers, date and conversion functions.

NVL function
The NVL function takes two arguments,

NVL(x, y)

where both x and y are expressions. This function returns y, if x is NULL. If x is not NULL, it returns x.

select comm;
nvl (comm,0)
from emp;

NVL Function converts all null values into different values , in above query it convert null values into 0,

Here is the list of some useful functions

LENGTH(s)      Returns the number of characters in the string s.      LENGTH('Welcome Sir') = 11

LPAD(s, i[, s1])Returns the string s expanded in length to i characters, using s1 to fill in space as needed on the left side.      LPAD('test', 11, '*-') = '*-*-*-*test'

INITCAP(s)      Returns the string s with the first character of each word in uppercase and all others in lowercase.      INITCAP('hello SIR') = 'Hello Sir'

CONCAT(s1, s2)  Returns string s2 appended to s1.      CONCAT('Wel', 'come') = 'Welcome'

LOWER(s)      Returns the string s with all characters in lowercase.      LOWER('Welcome Sir') = 'welcome sir'

UPPER(s)      Returns the string s with all characters in upper case.      UPPER('Welcome Sir') = 'WELCOME SIR'

SUBSTR(s, pos[, len])      Returns the portion of the string s that is len characters long, beginning at position pos. If pos is negative, the position is counted backwards from the end of the string. The function returns NULL if len is less or equal to zero. If len is skipped, it returns the remaining of s.      SUBSTR('welcome', 4) = 'come'

INSTR(s, sub[, pos[, n]])      Returns the numeric character position in s where the n-th occurrence of sub is found. The search begins at the position pos in s. If the substring not found, it returns 0. If the pos is negative, the search is performed backwards (from right to left). The default values for pos and n are 1.      INSTR('test', 't', 2) = 4

Some Numeric Functions

ROUND(d, i)      Returns d rounded to i digits of precision to the right of the decimal point. If i  is negative, d is rounded to the left of the decimal point. 

TRUNC(d, i)      Returns d truncated to i digits of precision to the right of the decimal point. If i  is negative, d is truncated to the left of the decimal point. 

MOD(i1, i2)      Returns i1 modulo i2, or the reminder of i1 divided i2.

Date and time Functions (Conversion)

 TO_CHAR(x[, fmt[, nls]])      Takes up to three arguments, where x is either a date or a number, fmt is a format string specifying the format that x will appear in, and nls specifies language or location formatting string.
If x is a date, fmt is a date format code.
If x is a number, fmt is a numeric format code.

TO_DATE(s[, fmt[, nls]])     Converts string s to DATE datatype accordingly the format string fmt. The fmt string uses the same date format code. The default is the value stored in the NLS_DATE_FORMAT session variable.

Decode Function is same like if else statements

 DECODE(x, v1, r1[, v2, r2 ...][, d])      The function compares the value of the expression x with values of the arguments v1, v2, etc. If the value of x is equivalent to v1, then r1 is returned; otherwise the additionalmatching expressions are compared. If no match is found and the default expression d is included, then the d  is returned. If no match is found and there is no d argument, then NULL is returned.
Example:

SELECT sid, serial#, username,
       DECODE(command, 0, 'None',
                       2, 'Instert',
                       3, 'Select',
                       6, 'Update',
                       7, 'Delete',
                       8, 'Drop',
                       'Other') cmd
  FROM v$session
 WHERE type <> 'BACKGROUND';

Click here to See all other functions or download the attachment below.