Single-row oracle Functions

Single-row oracle Functions
Zookeeper

Single Row Function
①. Character Functions
LOWER (x): converts each word in x to lowercase.
UPPER (x): converts every word in x to uppercase.
INITCAP (x): converts the initial letter of each word in x to uppercase.
CONCAT (x, y): Used to add y to x, same as |
SUBSTR (x, start [, length]): used to obtain a substring from x starting from the start position,
Optional parameter length: the length of the sub-string. If not specified, it is obtained until the end of the string.
LENGTH (x): returns the number of characters in x.
INSTR (x, find_string [, start] [, occurence]): searches for find_string in x and returns the position of find_string,
Optional parameter start: indicates which position in x to start searching. The first position is 1.
Optional parameter occurence: Specifies the position where find_string appears.
LPAD (x, width [, pad_string]): Fill the left side of x with spaces to make the total length reach width
Optional parameter pad_string: if given, pad the left side of x with the pad_string character
RPAD (x, width [, pad_string]): Fill the right side of x with spaces to make the total length reach width characters
Optional parameter pad_string: if given, add the pad_string character to the right of x.
TRIM (x [, trim_string]): used to remove spaces from the left and right sides of x.
Optional parameter trim_string: if specified, the trim_string characters are truncated from the left and right sides of x.
REPLACE (x, search_string, repalce_string): used to search for search_string in x and REPLACE it with replace_string.
② Numeric Functions
ROUND: ROUND (x [, y]) is used to calculate the integer of x.
Optional Parameter y: indicates the number of decimal places to be rounded up. If y is not specified, x is rounded up at 0 decimal places.
If y is negative, x is rounded to the | y | bit on the left of the decimal point.
Truncation: TRUNC (x [, y]) is used to calculate the result of x truncation.
Optional Parameter y: specifies the number of decimal places to be truncated. If y is not specified, x is truncated at 0 decimal places.
If y is a negative number, x is truncated at | y | bit on the left of the decimal point.
Remainder: MOD (x, y): used to calculate the remainder obtained by dividing x by y.
③ Date functions
MONTHS_BETWEEN (date1, date2): number of months with different dates
ADD_MONTHS (date1, addMonth): add the number of months to the specified date
NEXT_DAY (date1, 'monday'): returns the date of the next Monday.
LAST_DAY (date1): the last day of the month
ROUND (date1, 'Year'): ROUND date1 by year. month, day, hh, and mi are months, days, hours, and minutes, respectively.
TRUNC (date, 'month'): truncates date1 by year. month, day, hh, and mi are month, day, hour, and minute, respectively.
④ Conversion functions
TO_CHAR (date, 'format _ model'): format date: "yyyy-mm-dd HH24: MI: SS" in format_model format. A string is returned.
TO_CHAR (number, 'format _ model'): formatted number: "$99,999.99" in format_model format. A string is returned.
TO_DATE (char [, 'format _ model']): Use the TO_DATE function to convert the character into 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"
⑤ Common functions: These functions apply to any data type and null values.
NVL (expr1, expr2): If expr1 is null, expr2 is returned. If not null, expr1 is returned.
NVL2 (expr1, expr2, expr3) If expr1 is not null, expr2 is returned. If expr1 is null, expr3 is returned.
NULLIF (expr1, expr2): returns NULL if expr1 is equal to expr2, and returns expr1 if not.
COALESCE (expr1, expr2,..., exprn): refer to the parameter expressions in sequence. If a non-null value is returned, it is stopped.
If all expressions are null, a null value is returned. COALESCE is used because most expressions that contain null values will eventually return NULL values.
⑥ Conditional expression
1) CASE expression
Case... when... then ..
The Case expression can be used to determine the logic of if... then... else in SQL, instead of the effective PL/SQL method.
There are two types of SQL statements: Simple case and search case. There are two types of case statements in plsql, which are similar to decode. (There are some differences)
Simple case:
Syntax: case exp when comexp then returnvalue
.. When comexp then returnvalue
Else Returnvalue
End
Introduction to rule case.
A Case to end is equivalent to a specific value. It can be used for operations, aliases, nested cases, and so on. You only need to use case to end as an expression for the calculation result.
Note: No punctuation marks are available from the middle to the end.

Search case:
SELECT cust_last_name,
CASE credit_limit WHEN 100 THEN 'low'
WHEN 5000 THEN 'high'
ELSE 'medium'
END
FROM MERs MERS;

Search case examples

Select case when id between 1 and 10 then 'low'
When id between 20 and 30 then 'mid'
When id between 40 and 50 then 'high'
Else 'unknow'
End
From product;

Update emp set
Salary =
Case when salary <2000 then salary * 1.1
When salary between 2000 and 4000 then salary * 1.05
When salary> 4000 then salary * 1.04
Else
Null
End;

Select case when name like 'Global % 'then 'true'
When name like 'shenzhouhang 'then' false'
Else 'mm'
End
From trademark;
For comparison operations, you can use like,... And ..,! =, <,> = And other operators that return the boolean type.

Differences between simple case and searched case:
1. Simple case can only be the expression after when that matches the case completely, which is equal to =, so it cannot match null.
2. searched case can be used as a comparison condition, so you can use like ,! =, Between... and, <, =, is null, is not null, etc., is more widely used than a simple case, can completely replace a simple case.

2) DECODE Function
If-then-else logic in DECODE
In logical programming, If-Then-Else is often used for logical judgment. In DECODE syntax, this is actually the logic processing process.
Its syntax is as follows:
DECODE (value, if1, then1, if2, then2, if3, then3,... else)
Value indicates any column of any type in a table or any result obtained by calculation.
When each value is tested, if the value is if1, the result of the Decode function is then1;
If the value is equal to if2, the result of the Decode function is then2; and so on. In fact, multiple if/then pairs can be provided.
If the value result is not equal to any given pairing, the Decode result returns else.

Note that if, then, and else can both be functions or computing expressions.
7. nested Functions
F3 (F2 (F1 (col, arg1), arg2), arg3)
Single-row functions can be nested. The execution sequence of nested functions is from inner to outer.

In oracle, substr is a single row function.

Of course, it is a single-row function.

A single-row function is a function that only operates data on a row during each execution, for example, to_char (), to_date (), instr (), and so on.

A multiline function is a function that operates on all rows or all rows in each group after an operation is performed, such as count (), sum (), and avg (), max (), etc.

Common single-row functions in oracle databases include

LOWER ('SQL course') = SQL Course
UPPER ('SQL course') = SQL Course
CONCAT ('good', 'morning') =
SUBSTR ('string', 1, 3) = Str
LENGTH ('string') = 6
INSTR ('string', 'R') = 3
ROUND (45.926, 2) = 45.93
TRUNC (45.926, 2) = 45.92
TO_CHAR
NVL
DECODE
AVG
COUNT
MAX
MIN
SUM
And so on.