Oracle Functions-Single-line function-character line function

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/8C/16/wKiom1hhItLi-gZ4AABh8bZO-Vc870.png-wh_500x0-wm_3 -wmp_4-s_3809494292.png "title=" 1.png "alt=" Wkiom1hhitli-gz4aabh8bzo-vc870.png-wh_50 "/>

Classification of functions

=============================================================

Single-line function: One input corresponds to a output,input and output one by one corresponding relationships such as lower

Group function: multiple input, but only one output. such as SUM ()

=============================================================

Single-line function

Characteristics:

Each row returns one result, and the input output has a one by one corresponding relationship

can be nested use, the output of one function can do the input of another function such as: SELECT Lowner (Upper (' AA ')) from dual;

The passed-in variable can be either a column value or an expression. such as select lower (ename) from EMP;

=============================================================

Character functions:

1, case processing (upper lower), on string

Sql>select Upper (ename), Sal from EMP;

Sql>select Lower (ename), Sal from EMP;

2. Connection function concat

Sql>select concat (' Hello ', ' world ') from dual;

3. Interception of substr (C1,N1,N2)

N1 position at the beginning of the

The length of the N2 intercept, by default, is truncated to the last.

Sql>select substr (' HelloWorld ', 6,2) from dual;

4. InStr specifies the number of characters in a string

Sql>select InStr (' ABCDEFG ', ' C ') from dual; C in the string xxx of the first few

Sql>select InStr (' ABCDEFG ', ' C ', -1) from dual; From the right to the left number, the first C in the third parameter indicates the direction of the lookup

Sql>select InStr (' ABCDEFGC ', ' C ', -1) from dual;

Sql>select InStr (' ABCDEFGC ', ' C ', -1,2) from dual;

5. Take long length

Select Length (' Heloworld ') from dual;

Select Length (') from dual;

6, the number of digits filled

① left padding lpad (C1,N,C2) returns a string of the specified length n

Sql>select Lpad (sal,10, ' * ') from EMP;

② Right padding

Sql>select rpad (sal,10, ' * ') from EMP;

7. Trimming Trim

Sql>select trim (' Hello ') from dual; -intercept, both sides of the space. Effect message Board space problem, may accidentally hold a lot of space

Sql>select ltrim (' Hello ') from dual;

Sql>select RTrim (' Hello ') from dual;

8. Example

Sql>select Empno,concat (' name is ', ename) name,job,length (ename), InStr (ename, ' a ') "contains ' a '"

From EMP

where substr (job,5) = ' GER ';

This article is from "Rookie Talent" blog, please be sure to keep this source http://omphy.blog.51cto.com/7921842/1886341

Oracle Functions-Single-line function-character line function