Oracle's single-line function



Single-line function
①. Character functions
LOWER (x): Converts each word in X to lowercase
UPPER (x): Converts each word in X to uppercase
Initcap (x): Converts the first letter of each word in X to uppercase
CONCAT (x, y): Used to add y to X, with | | Functions the same
SUBSTR (X,start[,length]): Used to get a substring starting from the start position from X,
Optional parameter length: Specifies the length of the substring, if not given, to the end of the string
LENGTH (x): Returns the number of characters in X
INSTR (X,find_string[,start][,occurence]): Finds find_string in X, returns where Find_string is,
Optional parameter start: Describes where to start the search from X, and the first position is 1.
Optional parameter occurence: Description should return find_string where the first occurrence
Lpad (x, Width [, pad_string]): Used to add the left side of X to a space, so that the total length reaches the width character
Optional parameter pad_string: If given, the left side of X is padded with pad_string characters
Rpad (x,width[,pad_string]): Used to make the right side of x blank, so that the total length reaches width characters
Optional parameter pad_string: If given, the right side of X is padded with pad_string characters
TRIM (x[,trim_string]): Used to intercept some spaces from the left and right sides of X
Optional parameter trim_string: If given, some trim_string characters are truncated from the left and right sides of X
Replace (x,search_string,repalce_string): Used to find search_string in X and replace it with replace_string
② Numeric functions
Rounded: ROUND (X[,y]) used to calculate the rounding result of X
Optional parameter y: The description of the number of decimal rounding, if not specified y, the x at 0 decimal places rounding
If Y is negative, the X is rounded at the |y| bit to the left of the decimal point
Truncation: TRUNC (X[,y]) is used to calculate the result of X truncation
Optional parameter y: Indicates truncation of the fractional number and truncation of x at 0 decimal places if y is not specified
If Y is a negative number, the X is truncated at the |y| bit to the left of the decimal point
Balance: MOD (x, y): Used to calculate the remainder of x divided by Y
③ Date function
Months_between (DATE1,DATE2): Two months of difference in date
Add_months (date1,addmonth): Add a number of months to the specified date
Next_day (date1, ' Monday '): Returns the date of the next Monday
Last_day (date1): Last day of the month
ROUND (Date1, ' year '): Round the date1 on a yearly basis, month, day, HH, MI are rounded by monthly, date, time, and minute respectively.
TRUNC (date, ' month '): Truncate the date1 by year, month, day, HH, MI, respectively, on a monthly, daily, hourly, and minute basis.
④ conversion function
To_char (date, ' Format_model '): Formats the date: "Yyyy-mm-dd HH24:MI:SS" in Format_model format, returning a string
To_char (number, ' Format_model '): Format number: "$99,999.99" in Format_model format, returning a string
To_date (char[, ' Format_model '): Use the To_date function to convert characters to a date in Format_model format: "Yyyy-mm-dd HH24:MI:SS"
To_number (char[, ' Format_model '): Use the To_number function to convert characters into numbers in Format_model format: "$99,999.99"
⑤ General functions: These functions are suitable for any data type and also for null values
NVL (Expr1, EXPR2): Returns EXPR2 if EXPR1 is null, or EXPR1 if not null
NVL2 (Expr1, EXPR2, EXPR3) if EXPR1 is not NULL, returns EXPR2, or null, EXPR3
Nullif (Expr1, expr2): Expr1 equals expr2 return null, unequal return EXPR1
COALESCE (EXPR1, expr2, ..., exprn): Refer to each parameter expression in turn, and then stop and return the value if a non-null value is encountered.
If all of the expressions are NULL, a null value will eventually be returned. The use of coalesce is that most expressions that contain null values will eventually return null values.
⑥ conditional expression
1) Case expression
Case: When: Then..
The case expression, which can be used in SQL if: Then.. else, and avoid the effective method of using PL/SQL.
There are two main types of sql: The simple case and the search case, and in the Plsql, there are two kinds of scenario statements, similar to decode. (There is a certain difference)
A simple case:
Syntax: Case EXP If Comexp then returnvalue
.. When Comexp then returnvalue
Else returnvalue
End
Introduction to using Rule case.
The case to end is equivalent to a specific value that can be used for operations, aliases, nested case, and so on. Just treat case to end as an expression of the result of the operation.
"Notice that there's no other punctuation in the middle until end."

Search Case:
SELECT Cust_last_name,
Case Credit_limit when "low"
When the "high"
ELSE ' Medium '
END
From customers;

Search Case Examples

Select Case when ID between 1 and ten then ' low '
When ID between and then ' mid '
When ID between
Else ' Unknow '
End
from product;

Update EMP Set
salary=
Case if salary<2000 then salary*1.1
When salary between and 4000 then salary*1.05
When salary>4000 then salary*1.04
Else
Null
End

Select Case when the name like ' Global% ' then ' true '
When name is like ' Shenzhou line ' then ' false '
Else ' mm '
End
from trademark;
Compare operations, you can use Like,between ... and.,!=,<,>=, and other operators that return a Boolean type.

The difference between a simple case and a searched case:
1. A simple case can only be an expression after the when expression exactly matches the case, equivalent to =, and therefore cannot match null.
2. Searched case can be used as a comparison condition, then you can use Like,!=,between. And,<,=,is null,is NOT NULL, and so on, is more widely used than simple case, and can replace simple case completely.

2) DECODE function
The If-then-else logic in DECODE
In logic programming, If–then–else is often used to make logical judgments. In Decode's syntax, this is actually the logical process.
Its syntax is as follows:
DECODE (value, IF1, Then1, If2,then2, If3,then3, ... else)
Value represents any column of any type of a table or any result that is computed.
When each value is tested, if value is the result of the If1,decode function is then1;
If value equals the If2,decode function, the result is then2; In fact, multiple if/then pairs can be given.
If the value result is not equal to any given pairing, the Decode result returns else.

It is important to note that if, then, and else here can be functions or evaluation expressions.
⑦ nested functions
F3 (F2 (F1 (COL,ARG1), arg2), ARG3)
Single-line functions can be nested. The order in which the nested functions are executed is from the inside out

Oracle's single-line function