Oracle Common SQL functions

oracle| function Time: 2005-02-18
Oracle's SQL functions are divided into single-line functions and multiline functions. One-line functions are valid only for a single record, and multi-line functions are valid for multiple records.

Single-line functions include characters, numbers, dates, transformations, and normal functions.

Examples of character functions: ·

All lowercase LOWER (' SQL Course ') SQL course

All Capitals UPPER (' SQL Course ') SQL Course

• Initial capital Initcap (' SQL Course ') SQL Course concatenation CONCAT (' Good ', ' string ') goodstring fetch substring SUBSTR (' string ', 1,3) Str asks Length lengths (' String ') 6

Example of a number function: ROUND--Rounding the value trunc--truncation. Note the number of bits truncated and rounded, which can be negative. ROUND (45.923, 2) 45.92 ROUND (45.923, 0) ROUND (45.923,-1) TRUNC (45.923, 2) 45.92 TRUNC (45.923) 4 5 TRUNC (45.923,-1) 40

MOD--Returns the remainder of the division of two numbers

MOD (1600, 300)--100

Date function: Oracle The date into the following seven bytes-century, year, month, day, time, minute, second, in internal format. • The default display format for dates in Oracle is: Dd-mon-yy. • Can use system variable sysdate to get system time ·. When viewing sysdate, you can use virtual table dual.

For example: Find out how many months in two days    months_between (' 01-sep-95 ', ' 11-jan-94 ')       1.9774194 Add the specified number of months to the date          add_months (' 11-jan-94 ', 6)                                   ' 11-jul-94 ' What is the next one weeks        Next_day (' 01-sep-95 ', ' FRIDAY ')                            ' 08-sep-95 ' The last day of the month                               last_day (' 01-sep-95 ')                                             ' 30-sep-95 '

Rounding dates to month             ROUND (' 25-may-95 ', ' MONTH ')                                01-jun-95                                                       ROUND (' 25-may-95 ', ' year ')                                    01-jan-95 Date truncated to month                      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 a format string is used in a conversion function

The To_char (date, ' FMT ') format string is contained in ', case sensitive, and can be any valid date format string: YYYY represents the complete four-digit year, the year in which English spelling is expressed in two-digit months month the year dy of the whole spelling of English A three-bit abbreviation for week day that represents the full spelling of the week in English

Some special uses: Time format HH24:MI:SS AM 15:45:32 pm Add string to the format string DD "of" MONTH of October the entire date with a suffix Ddspth Fourteenth

To_char (number, ' FMT ') uses this function to convert numbers to a number of 9, indicating that the number 0 forces 0 $ to set the dollar sign L use the currency symbol of the current character set. decimal point, thousand separator

You can use To_number to convert a string to a number you can use To_date to convert a string to a format date to_date (' September 1992 ', ' DD Month YYYY ')

Use format string to_date (char [, ' FMT '])



normal function null value processing function

NVL (EXPR1, EXPR2) If EXPR1 is null, returns EXPR2NVL2 (Expr1, EXPR2, EXPR3) If EXPR1 is Non-null, returns EXPR2 if EXPR1 is null and returns EXPR3NULLIF (E XPR1, EXPR2) if EXPR1=EXPR2, return null, otherwise return EXPR1

COALESCE (EXPR1, expr2, ..., exprn) returns the first non-empty expression in an expression list

conditional expressions, using case expressions or decode to implement conditional logic case expressions Expr when comparison_expr1 THEN return_expr1 [when Comparison_expr2 THEN re TURN_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)

Author Blog: