Oracle Entry second day (bottom)-Single-line function

I. Overview

　　What is a single-line function:

Action data Object Accept parameter returns a result only one row is transformed each row returns a result that can be transformed data types can be nested parameters can be a column or a value

　

　　For example, a grouping function is a typical multi-line function

　　　Classification:

　　

second, character function

　　Casing Control Functions:

LOWER ()--Convert to lowercase

　　　　UPPER ()--Convert to uppercase

　　　　Initcap ()--Capitalize first letter

SELECT LOWER('SQL Course'),UPPER('SQL Course'), Initcap ('SQL Course') fromdual;LOWER('Sqlcourse')UPPER('Sqlcourse') Initcap ('Sqlcourse')------------------ ------------------ --------------------SQL Course SQL Course SQL Course

　　Dual is a continuation of the table, and again to remind SQL case insensitive, lower () is also allowed

　　Character control functions:

CONCAT (' Hello ', ' world ')--Connection string

　　　　SUBSTR (' HelloWorld ', 1,5)-take a substring, starting with subscript 1, fetch 5 (SQL subscript starting from 1 instead of 0)

　　　　Length (' HelloWorld ')--Get the lengths

　　　　INSTR (' HelloWorld ', ' W ')--The first occurrence of the position

　　　　Lpad (salary,10, ' * ')--left-padded

　　　　Rpad (Salary, ten, ' * ')--right-padded

　　　　TRIM (' H ' from ' HelloWorld ')--Removes the specified character from the end

　　　　Replace (' abcd ', ' B ', ' m ')--Replaces all characters (replaces B with M)

SELECTCONCAT ('HelloWorld','Jiangbei'), SUBSTR ('HelloWorld',1,5), LENGTH ('HelloWorld'), INSTR ('HelloWorld','W') fromdual; CONCAT ('HELLOWORLD','Jiangbei'SUBSTR ('HELLOWORLD',1,5) LENGTH ('HELLOWORLD') INSTR ('HELLOWORLD','W')------------------------------ ------------------------ -------------------- -----------------------Helloworldjiangbei HelloTen                       6

SELECTLpad (Salary,Ten,'*') fromemployees; Lpad (SALARY,Ten,'*')--------------------*****24000*****17000*****17000******9000******6000

SELECTTRIM ('H'  from 'h2h2h'),REPLACE('H2H2H2','H','M') fromdual; TRIM ('H' from'h2h2h')REPLACE('H2H2H2','H','M')-------------------- -------------------------2H2 m2m2m2

three, number function

　　ROUND (45.926,2)--Rounding

　　TRUNC (45.926,2)--truncation

　　MOD (100,3)--seeking redundancy

SELECT ROUND(45.926,2), TRUNC (45.926,2), MOD ( -,3) fromdual;ROUND(45.926,2) TRUNC (45.926,2) MOD ( -,3)--------------- --------------- ----------          45.93           45.92          1

Oracle Entry second day (bottom)-Single-line function