ORACLE FUNCTIONS

INITCAP – The Oracle/PLSQL INITCAP function sets the first character in each word to uppercase and the rest to lowercase.

INITCAP(‘tech on the net’);

Result: ‘Tech On The Net’

 

INSTR – The Oracle/PLSQL INSTR function returns the location of a substring in a string.

INSTR(‘Tech on the net’, ‘e’, 1, 2)

Result: 11  (the second occurrence of ‘e’)

 

INSTR(‘Tech on the net’, ‘e’, -3, 2)

Result: 2

 

LPAD => LPAD( string1, padded_length [, pad_string] )

LPAD(‘tech’, 7); => Result: ‘   tech’

LPAD(‘tech’, 8, ‘0’); => Result: ‘0000tech’

 

RPAD => RPAD( string1, padded_length [, pad_string] )

RPAD(‘tech’, 7) => Result: ‘tech   ‘

RPAD(‘tech’, 2) => Result: ‘te’

RPAD(‘tech’, 8, ‘0’) =>  Result: ‘tech0000’

 

LTRIM => LTRIM( string1 [, trim_string] )

-The LTRIM function may appear to remove patterns, but this is not the case as demonstrated in the following example.

-LTRIM(‘xxyyxzyxyyxTech’, ‘xyz’) => Result: ‘Tech’

-It actually removes the individual occurrences of ‘x’, ‘y’, and ‘z’, as opposed to the pattern of ‘xyz’.

-The LTRIM function can also be used to remove all leading numbers as demonstrated in the next example.

-LTRIM( ‘637Tech’, ‘0123456789’) => Result: ‘Tech’

-In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all leading numbers will be removed by the LTRIM function.

 

RTRIM => Same as LTRIM

 

REPLACE => REPLACE function replaces a sequence of characters in a string with another set of characters.

REPLACE( string1, string_to_replace [, replacement_string] )

REPLACE(‘123123tech’, ‘123’); => Result: ‘tech’

REPLACE(‘123tech123’, ‘123’); => Result:’tech’

REPLACE(‘222tech’, ‘2’, ‘3’); => Result: ‘333tech’

REPLACE(‘0000123’, ‘0’); =>  Result: ‘123’

REPLACE(‘0000123’, ‘0’, ‘ ‘); => Result: ‘    123’

 

SUBSTR => SUBSTR functions allows you to extract a substring from a string.

SUBSTR( string, start_position [, length ] )

Note: If start_position is 0, then the SUBSTR function treats start_position as 1 (ie: the first position in the string).

-If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.

-If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.

-If length is a negative number, then the SUBSTR function will return a NULL value.

SUBSTR(‘This is a test’, 6, 2) => Result: ‘is’

SUBSTR(‘This is a test’, 6) => Result: ‘is a test’

SUBSTR(‘TechOnTheNet’, 1, 4) => Result: ‘Tech’

SUBSTR(‘TechOnTheNet’, -3, 3) => Result: ‘Net’

SUBSTR(‘TechOnTheNet’, -6, 3) => Result: ‘The’

SUBSTR(‘TechOnTheNet’, -8, 2) => Result: ‘On’

 

 

Reverse => to reverse string

select reverse(ename) from emp

 

TRANSLATE => TRANSLATE function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.

 

TRANSLATE( string1, string_to_replace, replacement_string )

TRANSLATE(‘1tech23’, ‘123’, ‘456’) => Result: ‘4tech56’

TRANSLATE(‘222tech’, ‘2ec’, ‘3it’) => Result: ‘333tith’

 

ABS => ABS function returns the absolute value of a number.

ABS(-23) => Result: 23

 

CEIL => CEIL function returns the smallest integer value that is greater than or equal to a number

CEIL(32.1) => Result: 33

CEIL(-32.65) =>  Result: -32

 

FLOOR is opposite of CEIL.

 

COUNT => Only includes NOT NULL Values

Not everyone realizes this, but the COUNT function will only include the records in the count where the value of expression in COUNT(expression) is NOT NULL. When expression contains a NULL value, it is not included in the COUNT calculations.

 

GREATEST => GREATEST(‘apples’, ‘applis’, ‘applas’) Result: ‘applis’

LEAST(‘apples’, ‘applis’, ‘applas’, null)  Result: NULL

LEAST(‘apples’, ‘applis’, ‘applas’) Result: ‘applas’

 

MOD => MOD(11.6, 2) => Result: 1.6

MOD(-15, 0) => Result: -15

 

POWER => POWER(3, 2) Result: 9

 

ROUND => ROUND(125.315) Result: 125

ROUND(125.315, 0) Result: 125

ROUND(125.315, 1) Result: 125.3

ROUND(125.515) Result: 126

ROUND(125.515,1) Result: 125.5

 

 

SIGN => SIGN( number )

If number 0, then sign returns 1.

SIGN(-23) => Result: -1

SIGN(23.601) => Result: 1

 

TRUNC => TRUNC function returns a number truncated to a certain number of decimal places

TRUNC( number [, decimal_places] )

TRUNC(125.815)     Result: 125

TRUNC(125.815, 0)  Result: 125

TRUNC(125.815, 1)  Result: 125.8

TRUNC(-125.815, 2) Result: -125.81

TRUNC(125.815, -1) Result: 120

TRUNC(125.815, -2) Result: 100

TRUNC(125.815, -3) Result: 0

 

ADD_MONTHS(’01-Aug-03′, 3)

Result: ’01-Nov-03′

ADD_MONTHS(’01-Aug-03′, -3)

Result: ’01-May-03′

 

EXTRACT(YEAR FROM DATE ‘2003-08-22’) Result: 2003

EXTRACT(MONTH FROM DATE ‘2003-08-22’) Result: 8

EXTRACT(DAY FROM DATE ‘2003-08-22’) Result: 22

 

LAST_DAY(TO_DATE(‘2003/03/15’, ‘yyyy/mm/dd’)) Result: Mar 31, 2003

 

MONTHS_BETWEEN (TO_DATE (‘2003/01/01’, ‘yyyy/mm/dd’), TO_DATE (‘2003/03/14’, ‘yyyy/mm/dd’) )

would return -2.41935483870968

 

NEXT_DAY(’01-Aug-03′, ‘TUESDAY’) => Result: ’05-Aug-03′

NEXT_DAY(’06-Aug-03′, ‘WEDNESDAY’) => Result: ’13-Aug-03′

 

ROUND(TO_DATE (’22-AUG-03′),’YEAR’) Result: ’01-JAN-04′

ROUND(TO_DATE (’22-AUG-03′),’Q’) Result: ’01-OCT-03′

ROUND(TO_DATE (’22-AUG-03′),’MONTH’) Result: ’01-SEP-03′

ROUND(TO_DATE (’22-AUG-03′),’DDD’) Result: ’22-AUG-03′

ROUND(TO_DATE (’22-AUG-03′),’DAY’) Result: ’24-AUG-03′

 

TRUNC(TO_DATE(’22-AUG-03′), ‘YEAR’) Result: ’01-JAN-03′

TRUNC(TO_DATE(’22-AUG-03′), ‘Q’) Result: ’01-JUL-03′

TRUNC(TO_DATE(’22-AUG-03′), ‘MONTH’) Result: ’01-AUG-03′

TRUNC(TO_DATE(’22-AUG-03′), ‘DDD’) Result: ’22-AUG-03′

TRUNC(TO_DATE(’22-AUG-03′), ‘DAY’) Result: ’17-AUG-03′

 

CAST ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )

select CAST( ’22-Aug-2003′ AS varchar2(30) ) from dual;

 

 

TO_CHAR(1210.73, ‘9999.9’) Result: ‘ 1210.7’

TO_CHAR(-1210.73, ‘9999.9’) Result: ‘-1210.7’

TO_CHAR(1210.73, ‘9,999.99’) Result: ‘ 1,210.73’

TO_CHAR(1210.73, ‘$9,999.00’) Result: ‘ $1,210.73’

TO_CHAR(21, ‘000099’) Result: ‘ 000021’

 

COALESCE => COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null.

 

COALESCE( expr1, expr2, … expr_n )

 

SELECT COALESCE( address1, address2, address3 ) result

FROM suppliers;

The above COALESCE function is equivalent to the following IF-THEN-ELSE statement:

 

IF address1 is not null THEN

result := address1;

 

ELSIF address2 is not null THEN

result := address2;

 

ELSIF address3 is not null THEN

result := address3;

 

ELSE  result := null; END IF .;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s