Tutorials to .com

Tutorials to .com » Asp » Fso » Collection of commonly used functions of Oracle

Collection of commonly used functions of Oracle

Print View , by: iSee ,Total views: 9 ,Word Count: 1362 ,Date: Thu, 16 Apr 2009 Time: 4:53 AM

Value function:


abs (m) m the absolute value of
mod (m, n) m by n addition to the remainder after
power (m, n) m of the n-th power
round (m [, n]) m rounded to the decimal value of n-bit (n default of 0)
trunc (m [, n]) m cut-off n-bit decimal value of (n default of 0)



-------------------------------------------------- ------------------------------


Characters function:


initcap (st) return st to the first letter of each word uppercase, all other letters lowercase
lower (st) return st letter of each word will be all lowercase
upper (st) return st letter of each word will be all uppercase
concat (st1, st2) return st for st2 then at the end of st1 ("||") operator can be used
lpad (st1, n [, st2]) to return to the right alignment of the st, st for use in the left side of st1 until filled st2 length n, st2 default for the space
rpad (st1, n [, st2]) to return to the left aligned st, st for use in the right side of st1 until filled st2 length n, st2 default for the space
ltrim (st [, set]) return st, st for the deletion from the left in the character set is not until the first set of characters. By default, refers to the space
rtrim (st [, set]) return st, st for the right to delete from the set of characters is not until the first set of characters. By default, refers to the space
replace (st, search_st [, replace_st]) will appear each time st by search_st the replace_st replacement, to return to a st. By default, delete search_st
substr (st, m [, n]) n = return st string substring from position m to start, take n characters long. By default, the end has always been to return to the st
length (st) value, return the number of characters in st
instr (st1, st2 [, m [, n]]) value, the return st1 started from the first m characters, st2 first appear n times, m and n default value of 1
For example:
1.
select initcap ( 'THOMAS'), initcap ( 'thomas') from test;
initca initca
------ ------
Thomas Thomas
2.
select concat ( 'abc', 'def') "first" from test;
first
-----
abcdef
3.
select 'abc' | | '' | | 'def' "first" from test;
first
-----
abc def
4.
select lpad (name, 10), rpad (name, 5 ,'*') from test;
lpad (name, 10) rpad (name, 5 ,'*')
------------ ----------------
mmx mmx **
abcdef abcde
5.
Remove the address field of the point and the end of the word st and rd
select rtrim (address, '. st rd') from test
6.
select name, replace (name, 'a','*') from test;
name replace (name, 'a','*')
---- ---------------------
great gre * t
7.
select substr ( 'archibald bearisol', 6,9) a, substr ( 'archibald bearisol', 11) b from test;
ab
------- -------
bald bear bearisol
8.
select name, instr (name, '') a, instr (name, '', 1,2) b from test;
name ab
------- -------- ---------
li lei 3 0
lil 2 4

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Transfer function:
nvl (m, n) if the m value is null, the return of n, otherwise return m
to_char (m [, fmt]) m from a value converted to the specified format string fmt default, fmt value is able to accommodate the width of the effective number of all
to_number (st [, fmt]) st from the character data into numerical format specified, the default value when the size of the format string is the number for the whole
Attachment:
to_char () function of the format:
---------------------------------
Symbol Description
---------------------------------
9 results for each of the nine representatives of a number of
0 on behalf of the pilot to show 0
$ Dollar sign printed on the left side of the number of
L arbitrary local currency symbol
. Print the decimal metric system
, Printing on behalf of thousands of sub-spaces of the comma
---------------------------------
For example:
1.
select to_number ('123 .45 ') + to_number ('234 .56') form test;
to_number ('123 .45 ') + to_number ('234 .56')
----------------------------------------
358.01
2.
select to_char (987654321) from test;
to_char (987654321)
------------------
987654321
3.
select to_char (123, '$ 9999999') a, to_char (54321, '$ 9999999') b, to_char (9874321, '$ 9999999') c from test;
abc
------- ---------- -----------
$ 123 $ 54,321 $ 9,874,321
4.
select to_char (1234.1234,'999999 .999 ') a, to_char (0.4567,'999999 .999') b, to_char (1.1,'999999 .999 ') from test;
abc
--------- ---------- ------------
1,234.123 .457 1.100



-------------------------------------------------- ------------------------------


Sub-function:


avg ([distinct / all] n) out of the average of n
count ([all] *) return inquiries within the scope of the number of rows, including duplicate value and null value
count ([distinct / all] n) the value of non-empty rows
max ([distinct / all] n) the column or expression of the maximum
min ([distinct / all] n) the column or expression of the minimum
stdev ([distinct / all] n) the column or expression of the standard deviation, null ignored
sum ([distinct / all] n) the column or expression of the sum of
variance ([distinct / all] n) the column or expression of the variance, null ignored



-------------------------------------------------- ------------------------------


Date of function:

>

add_months (d, n) the date d plus n months
last_day (d) contains d, the last day of the month of the date of
month_between (d, e) the date d and e between the number of months, e before the d
new_time (d, a, b) a time zone the date and time of d in time zone b of the date and time
next_day (d, day) later than the date of d by the day specified in a few weeks of the date of
sysdate current system date and time
greatest (d1, d2, ... dn) the date given the date of the final list
least (d1, k2, ... dn) the date the list is given of the date of the earliest
to_char (d [, fmt]) Date d format specified by the fmt string into
to_date (st [, fmt]) string st in accordance with the format fmt specified value into the date, if fmt ignored, st use default format
round (d [, fmt]) Date d by the fmt format specified rounding to the nearest date
trunc (d [, fmt]) Date d by the fmt format specified to the nearest cut-off date
Attachment:
Date format:
--------------------------------
Description format code value, for example, or the scope of
--------------------------------
DD on a certain day of the month 1-3
DY three capital letters, said a few weeks of the SUN, ... SAT
DAY complete a few weeks, the English capital SUNDAY, ... SATURDAY
MM the month 1-12
MON three capital letters that month JAN, ... DEC
MONTH integrity JANUARY, ... DECEMBER
RM Roman numeral month I, ... XII
YY or YYYY two, four-digit years
HH: MI: SS pm: minutes: seconds [AutoPage]
HH12 or HH24 to 12 hours or 24 hours display
MI sub -
SS seconds
AM or PM indicator on the afternoon of
SP suffix SP spelling out the requirements of any numerical field
TH suffix added that the number of TH is the ordinal number of 4th, 1st
FM prefix of the months or days or annual value, the prohibition of filling
---------------------------------
For example:
1.
The date of next Friday
select next_day (sysdate, 6) from test;
2.
Two months ago, today's date
select add_months (sysdate, -2) from test;


ASP File System Object Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.