Oracle function-one-line function-character one-line function

Classification of functions

=

One-line function: an input corresponds to an output,input and there is an one-to-one correspondence between output,input and output, such as lower

Group function: multiple input, but only one output. Such as sum ()

=

One-line function

Features:

Each row returns a result, and there is an one-to-one correspondence between input and output.

Can be nested, the output of one function can be used as the input of another function, such as: select lowner (upper ('aa')) from dual

The variable passed in can be the value of a column or an expression. Such as select lower (ename) from emp

=

Character function:

1. Case handling (upper lower) for strings

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. Intercept the substr (C1, recital, N1, and N2)

Position of N1 at the beginning

N2 the length of the intercept, which defaults 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 what bit of the string xxx

SQL > select instr ('abcdefg','c',-1) from dual; is counted from right to left, with the first c in what position and the third parameter indicating the direction of search

SQL > select instr ('abcdefgc','c',-1) from dual

SQL > select instr ('abcdefgc','c',-1,2) from dual

5. Take the length length

Select length ('HeloWorld') from dual

Select length ('') from dual

6. Digit filling

① left padding lpad (c1Magneol c2) returns a string of specified length n

SQL > select lpad (sal,10,'*') from emp

② right fill

SQL > select rpad (sal,10,'*') from emp

7. Pruning trim

SQL > select trim ('Hello') from dual;-intercept the spaces on both sides. The problem with the space on the message board may have accidentally pressed a lot of spaces.

SQL > select ltrim ('Hello') from dual

SQL > select rtrim ('Hello') from dual

8. Examples

SQL > select empno,concat ('name is', ename) NAME,job,length (ename), instr (ename,'A') "contains 'A'"

From emp

Where substr (job,5) = 'GER'