Oracle SQL Functions

Oracle SQL Functions
Oracle SQL functions are divided into single-row and multi-row functions. A single-row function is only valid for a single record. A multi-row function is valid for multiple records.
Single-line functions include: characters, numbers, dates, conversions, and common functions.

Character function example:
? All lower-case lower ('SQL course') SQL Course
? All uppercase upper ('SQL course') SQL Course

? Capitalized initcap ('SQL course') SQL Course
Concatenate Concat ('good', 'string') goodstring
Substring substr ('string', 1, 3) Str
Length ('string') 6

Example of numeric functions:
Round -- rounding a value
Trunc -- truncation. Pay attention to the number of truncation and rounding digits, which can be a negative number.
Round (45.923, 2) 45.92
Round (45.923, 0) 46
Round (45.923,-1) 50
Trunc (45.923, 2) 45.92
Trunc (45.923) 45
Trunc (45.923,-1) 40

Mod -- returns the remainder of the division of two numbers.

MoD (1600,300) -- 100

Date functions:
? Oracle saves the date as the following seven Bytes: century, year, month, day, hour, minute, and second .? In Oracle, the default date display format is dd-mon-yy .? Can the system variable sysdate be used to obtain the system time ?. When viewing sysdate, you can use the virtual table dual.

Example:
Calculate the number of months in the two-day period months_between ('01-SEP-95 ', '11-Jan-94') 1.9774194
Add the specified number of months on the date add_months ('11-Jan-94 ', 6) '11-JUL-94'
When is the next day next_day ('01-SEP-95 ', 'Friday') '08-SEP-95'
Last day of a month last_day ('01-SEP-95 ') '30-SEP-95'

Round the date to the month round ('25-may-95 ', 'month') 01-jun-95
Round ('25-may-95 ', 'Year') 01-Jan-95
Trunc ('25-may-95 ', 'month') 01-may-95
Trunc ('25-may-95 ', 'Year') 01-Jan-95

Conversion functions

To_char converts a number or date to a string
To_number converts a string to a number.
To_date converts a string to a date
The format string is used in the conversion function.

To_char (date, 'fmt ')
Formatted string containing '', case sensitive, can be any valid date
Format
Format String:
Yyyy indicates the complete four-digit year.
Year indicates the year in English spelling.
MM indicates two-digit month
Month indicates the year of English spelling.
Dy indicates the week of the three abbreviations
Day indicates the week in full English.

Some special usage:
Time Format
Hh24: MI: SS am 15:45:32
Add a string to the Format String
Dd "of" Month 12 of October
Spell out the entire date with a suffix
Ddspth fourteenth

To_char (number, 'fmt ')
Use this function to convert a number to a number.
9 indicates a number
0: 0
$ Set the dollar sign
L use the currency symbol of the Current Character Set
. Decimal point
, Thousands Separator

You can use to_number to convert a string to a number.
You can use to_date to convert a string to a date in the format.
To_date ('10 September 1992 ', 'dd month yyyy ')

Format String
To_date (char [, 'fmt'])

Common functions
Null Value processing function

Nvl (expr1, expr2)
If expr1 is null, expr2 is returned.
Nvl2 (expr1, expr2, expr3)
If expr1 is not null, expr2 is returned. If expr1 is null, expr3 is returned.
Nullif (expr1, expr2)
If expr1 = expr2, null is returned; otherwise, expr1 is returned.

Coalesce (expr1, expr2,..., exprn)
Returns the first non-empty expression in the expression list.
　　

Conditional expressions. Use Case expressions or decode to implement conditional logic.
Case expression
Case expr when comparison_expr1 then return_expr1
[When comparison_expr2 then return_expr2
When comparison_exprn then return_exprn
Else else_expr]
End

Decode Function
Decode (COL | expression, search1, result1
[, Search2, result2,...,]
[, Default])

Grouping functions:
AVG (distinct | all | N)
Count (distinct | all | expr | *)
Max (distinct | all | expr)
Min (distinct | all | expr)
Stddev (distinct | all | N)
Sum (distinct | all | N)
Variance (distinct | all | N)