Shulou(Shulou.com)06/01 Report--

Oracle functions include character functions, mathematical functions, date functions and conversion functions. I only know character functions and mathematical functions.

1. Character function:

Lower (char) converts strings to lowercase format

SQL > select lower (ename), ename from emp

LOWER (ENAME) ENAME

--

Smith SMITH

Allen ALLEN

Ward WARD

Upper (char) converts strings to 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 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 with bit 2 and taking 3 bits. M is taken from the m, and the substring n of the string represents the meaning of n, not the 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 length of the string. HelloWorld has a total of 10 digits. Intercepts the name where ename is equal to 5, and only two ward are 4 in length, so they are not listed.

SQL > select length ('HelloWorld') from tb2

LENGTH ('HELLOWORLD')

-

ten

SQL > select ename from emp where length (ename) = 5

ENAME

-

SMITH

ALLEN

Replace (char1,search_string,replace_string) replace 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 the substring in the position of the string, and W in the sixth position

SQL > select instr ('HelloWorld','W') from tb2

INSTR ('HELLOWORLD','W')

six

Second, mathematical functions:

The data types of input parameters and return values of mathematical functions are numeric. The processing of numbers is most frequently used in the financial system or banking system. Different processing methods have different results on financial statements. Mathematical functions include cos,cosh,exp,ln, log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round, which are the most commonly used here:

Round (n, [m]) this function is used to perform rounding. If m is omitted, it is rounded to an integer, and if m is a positive number, it is rounded to m places after the decimal point. If m is negative, it is rounded before the m place of the decimal point.

Round to first 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

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

1 bit on the left is rounded

SQL > select round (211.5,-1) from dual

ROUND (211.5 Mai Mui 1)

-

two hundred and ten

SQL > select round (215.5,-1) from dual

ROUND (215.5 mai Yu 1)

-

two hundred and twenty

Two places on the left are rounded

SQL > select round (211.5,-2) from dual

ROUND (211.5 Mai Yu 2)

-

two hundred

SQL > select round (261.5,-2) from dual

ROUND (261.5 Mai Yu 2)

-

three hundred

Trunc (n, [m]) this function is used to intercept numbers. If m is omitted, the decimal part is truncated, if m is positive, the m place of the decimal point is truncated, and if m is negative, it is truncated to the first m place of the decimal point.

SQL > select trunc (45.923), trunc (45.923), trunc (45.923) from dual

TRUNC (45.923) TRUNC (45.923) TRUNC (45.923)

45.92 45 0

SQL > select trunc (45.923), trunc (45.923), trunc (45.923) from dual

TRUNC (45.923) TRUNC (45.923) TRUNC (45.923)

45.92 45 40

Mod (m ~ () n), find the remainder. The value is the remainder of mBO.

SQL > select mod (10L2) from dual

MOD (10Phone2)

-

0

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

SQL > select floor (sal), sal from tb2

FLOOR (SAL) SAL

--

500 500.34

500 500.56

585 585.56

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

SQL > select ceil (sal), sal from tb2

CEIL (SAL) SAL

--

501 500.34

501 500.56

586 585.56

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.