Oracle Study Notes 4-single row Functions

Functions can be used to complete a series of operations. The difference between databases is that the support for functions is different. The learning function of this note. Functions are divided into single-row functions and multi-row functions. Here we focus on single-row functions. Single-row function category: character functions numeric functions date functions conversion functions general functions character functions: case-sensitive control functions

Functions can be used to complete a series of operations. The difference between databases is that the support for functions is different. The learning function of this note. Functions are divided into single-row functions and multi-row functions. Here we focus on single-row functions. Single-row function category: character functions numeric functions date functions conversion functions general functions character functions: case-sensitive control functions

Functions can be used to complete a series of operations. The difference between databases is that the support for functions is different.

The learning function of this note. Functions are divided into single-row functions and multi-row functions. Here we focus on single-row functions.

Single Row function category:

Character Functions

Numeric Functions

Date Functions

Conversion functions

Common functions

Character functions: include case-sensitive and character-controlled functions.

1) case-insensitive functions:

LOWER ()

UPPER ()

INITCAP ()

?

1

2

3

4

5

SQL> select lower ('SQL course'), upper (' SQL course'), initcap ('SQL course') from dual;

LOWER ('sqlcourse') UPPER ('sqlcourse') INITCAP ('sqlcourse ')

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

SQL course SQL course SQL Course

The preceding query results show that:

The Lower () function converts characters to lowercase letters.

The Upper () function converts characters to uppercase.

The Initcap () function converts the initial character to uppercase.

For example, to query the employee information whose name is king, the lower function and upper () function are introduced to ensure the validity of the query because we are not sure about the case format of the database name.

?

1

2

3

4

5

6

7

8

9

10

11

SQL> select * from emp where lower (ename) = 'King ';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7839 king president 1981/11/17 5000.00 10

SQL> select * from emp where ename = upper ('King ');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7839 king president 1981/11/17 5000.00 10

For example, convert the first letter of the query result into an uppercase letter using the initcap function.

?

1

SQL> select initcap (ename) from emp where ename = 'King ';

?

1

2

3

INITCAP (ENAME)

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

King

Character control functions:

Concat ()

Substr ()

Length ()

Replace ()

For example, you can use the concat () function to connect strings in addition to '|.

?

1

2

3

4

5

6

7

8

9

10

11

SQL> select concat ('hello', 'World') from dual;

CONCAT ('hello', 'World ')

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

Hello world

SQL> select substr ('hello', 1, 2), length ('World'), replace ('SQL', 'Q', 'x') from dual;

SUBSTR ('hello', 1, 2) LENGTH ('World') REPLACE ('SQL', 'Q', 'x ')

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

He 5 sXl

In the above example, we can see that the function of substr () is to intercept a string, followed by two parameters. The first parameter represents

Start position. The second parameter indicates the number of interceptions. The length () function returns the length of the string. Replace () string followed by two

Parameters. The first parameter indicates the letter to be replaced, and the second parameter indicates replacing the first letter with the second letter.

In addition, Oracle supports reverse string truncation:

?

1

2

3

4

5

6

7

SQL> select ename, substr (ename,-3) from emp where deptno = 10;

Ename substr (ENAME,-3)

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

CLARK ARK

KING ING

MILLER LER

Numeric functions:

Round (): Rounding Function

Trunc (): truncates decimal places.

Mod (): returns the remainder function.

Example:

?

1

2

3

4

5

SQL> select round (234.12, 2), round (234.324), round (234.25,-2) from dual;

ROUND (234.12, 2) ROUND (234.324) ROUND (234.25,-2)

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

234.12 234 200

The following parameter 2 indicates that the last two digits of the decimal point are retained. If this parameter is not specified, the default value is 0.-2 indicates that the number before the decimal point is rounded to ROUND (234.25,-2) and rounded to 4, 3. Get 200.

Example:

?

1

2

3

4

5

SQL> select trunc (234.12, 2), trunc (234.324), trunc (248.25,-2) from dual;

TRUNC (234.12, 2) TRUNC (234.324) TRUNC (248.25,-2)

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