Oracle Database functions (single row functions)

Functions in Oracle are similar to functions in C. They are also composed of function names, parameter tables, and return value types. A single-row function has a result for each record. A single-row function can appear after select or in the where clause.

When you need to process the actual results, write the function after the select statement. When it is used for conditional filtering, the function is used in the where clause.

1. Character functions (varchar)

Characters are case sensitive.
Lower-case lower ('...')
Convert uppercase upper ('...')
Uppercase letter initcap ('...')
A dual table is used for Function Testing and computation. It has only one record.
String concatenation concat ('...','....')
Returns the substring substr ('...', starting position, number of characters) (the starting position can be negative)
Evaluate the length ('...... ')
You can use "-" to extract data from the right to the left. You can extract data from the left to the friends.
Example:
Select upper ('test') from dual;
Select first_name from s_emp where upper (first_name) = 'George ';
Select substr (first_name,-) sub from s_emp; (take the last two)
Select substr (first_name, 2, 2) sub from s_emp; (take the first two)

2. number)

Round (data, number of digits after the decimal point)
You can use a negative number to indicate the first digit after the decimal point, that is, the reserved digits, and-1 to indicate the digits (to 10 digits ).
Example: select round (15.36, 1) from dual;
Result: 15.4

Trunc (data, reserved digits (digits after decimal point)
Example: select trunc (123.456,-1) from dual; retained 10
Result: 120

3. Date Functions

Date Format,
Century information in full date format, year, month, day, hour, minute, second.
Default Date Format: Day-month-year dd-mon-rr
The date type can be calculated in mathematics. + 1 indicates the next day.-1 indicates the last day. The database implicitly converts the data.

Modify the date format of the current session. The date is output according to the specified format.
Alter session set nls_date_format = 'yyyy mm dd hh24: mi: ss ';

Returns the current date sysdate.
Example: select sysdate + 20 from dual;

Date is format-sensitive
Calculate the number of months of months_between (date1, date2)
Add_months (date, number of months) of the specified number of months, the number of months can be negative, and the negative value is minus the corresponding number of months.
Add one day next_day (date, days) from the date starting next week)

Example: select next_day (sysdate, 2) from dual;
Returns the last_day (date) of the last month)

Trunc (date, 'year, month, day, hour, second ')
Example: select trunc (add_months (sysdate, 1), 'month') from dual;

4. conversion functions between different data types

Convert a date to a character tochar (date, 'date format ')
The date format must be in a valid format. The format is case sensitive: 'yyyy mm dd hh24: mi: ss', 'Year' (full year), and 'mm' (the number indicates the month) 'month', 'day', 'ddspth', 'yy mm dd ', 'rr-mm-dd' (Century will be judged based on the year)
Example: select to_char (sysdate, 'yyyy mm dd hh24: mi: ss') from dual;

Convert the character to the number to_number ('...'). (You can add the second parameter. The second parameter specifies the hexadecimal number)

Convert a number to the character to_char (number, 'fmt'). fmt is a number format.

Convert string to date to_date ('...', 'date format ')
Example: select to_char (to_date ('1970 11 03', 'yyyy mm dd'), 'dd-month-yy ')
From dual;

5. Function nesting

Example: select to_char (to_date ('1970 11 03', 'yyyy mm dd'), 'dd-month-yy ')
From dual;