Oracle Function Learning oracle10g Learning Series (7)

Oracle functions have character functions, mathematical functions, date functions, and conversion functions. I've only learned about character functions and math functions.

one, character functions :

Lower (char) converts a string to a lowercase format

Sql> Select Lower (ename), ename from EMP;

LOWER (ename) ename

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

Smith Smith

Allen Allen

Ward Ward

Upper (char) converts a string to an uppercase format

Sql> Select Upper (ename), ename from EMP;

UPPER (ename) ename

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

Smith Smith

Allen Allen

Ward Ward

Initcap (char) converts the first letter of a string to an uppercase format

Sql> Select Initcap (ename), ename from EMP;

Initcap (ename) ename

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

Smith Smith

Allen Allen

Ward Ward

SUBSTR (char,m,n) takes the substring of the string, starting from the 2nd bit, taking 3 bits. M is taken from the beginning of the first m, taking the substring of the string n represents the meaning of the N, not the delegate fetch to Nth

Sql> Select substr (ename,2,3), ename from EMP;

SUBSTR (ename,2,3) ename

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

MIT SMITH

LLE ALLEN

ARD WARD

Length (char) returns the lengths of the strings, intercepts the name ename equals 5, and only two ward's length is 4, so it is not listed to

Sql> select ename from emp where length (ename) = 5;

Ename

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

SMITH

ALLEN

Replace (char1,search_string,replace_string) Replacement function, replace a with Bob

Sql> Select Replace (ename, ' A ', ' Bob '), ename from EMP;

REPLACE (ename, ' A ', ' BOB ') ename

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

Smith Smith

Bobllen ALLEN

WBOBRD WARD

InStr (Char1,char2,[,n[,m]]) takes a substring in the position of a string

No examples

Second, mathematical function:

The input parameters and the data type of the return value of the mathematical function are numeric types. The processing of numbers, the most used in the financial system or the banking system, different processing methods, have different results for the financial statements. Mathematical functions include Cos,cosh,exp,ln, Log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round, which are most commonly used here:

Round (n,[m]) This function is used to perform rounding, if M is omitted, rounded to an integer if M is positive, rounded to the M-bit of the decimal point. If M is a negative number, it is rounded to the M-bit of the decimal point.

Rounded to 1th place

Sql>select round (SAL), Sal from EMP;

ROUND (SAL) Sal

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

800 800.34

1601 1600.56

1251 1250.78

Sql>select round (sal,1), Sal from EMP;

ROUND (SAL) Sal

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

800.3 800.34

1600.6 1600.56

1250.8 1250.78

Trunc (N,[m]) This function is used to intercept numbers. If M is omitted, the fractional portion is truncated and if M is a positive number, the M-bit of the decimal point is truncated, and if M is a negative number, the first m bit of the decimal point is intercepted.

Sql>select round (SAL), Sal from EMP;

ROUND (SAL) Sal

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

800 800.34

1600 1600.56

1250 1250.78

Sql>select round (sal,1), Sal from EMP;

ROUND (SAL) Sal

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

800.3 800.34

1600.5 1600.56

1250.7 1250.78

MoD (m,n), value is the remainder of m/n

Floor (n) returns the largest integer less than or equal to n

Ceil (n) returns the smallest integer greater than or equal to n

This article is from "Kaka West" blog, please be sure to keep this source http://whnba.blog.51cto.com/1215711/1599871

Oracle Function Learning oracle10g Learning Series (7)