Keywords: Oracle numeric function-type date conversion To_char automatic type conversion Date type numeric converter other function numeric function
ABS: Find the absolute value function, such as: ABS (−5) 5
SQRT: square root function, such as: sqrt (2) 1.41421356
Power: exponentiation function, such as: 2,3 8
COS: cosine trigonometric functions, such as: cos (3.14159) −1
MoD: Find division remainder, such as mod (1600, 300) 100
Ceil: The smallest integer greater than or equal to a number, such as: Ceil (2.35) 3
Floor: To find the largest integer less than or equal to a number, such as: Floor (2.35) 2
Round: Rounding decimal numbers by specified precision, such as: Round (45.923, 1), result, 45.9
Round (45.923, 0), results, 46
Round (45.923,−1), results, 50
TRUNC: Truncate decimal digits by specified precision, such as: Trunc (45.923, 1), result, 45.9
Trunc (45.923), results, 45
Trunc (45.923,−1), results, 40
Practice using numeric functions.
Use ABS to find the absolute value function.
SQL code SELECT abs (? 5) from dual;
SELECT ABS (? 5) from dual;
Execution results:
ABS (? 5)
---------------
5
Description: The absolute value of 5, the result is 5.
Character type function
ASCII: Gets the ASCII code for the character, such as: ASCII (' A ') 65
CHR: Returns the character corresponding to the ASCII code, such as: Chr (a)
Lower: convert string to lowercase, such as: Lower (' SQL Course ') SQL Course
Upper: Converts a string to uppercase, such as: Upper (' SQL Course ') SQL Course
Initcap: Converts a string to an uppercase start for each word, such as: Initcap (' SQL Course ') SQL course
Concat: Connect two strings concat (' sql ', ' Course ') SQL Course
SUBSTR: gives the starting position and length, returns the substring, such as: substr (' string ', 1,3) Str
Length: Lengths to find String (' wellcom ') 7
InStr: Gives the starting position and the number of occurrences, and asks the position of the substring to appear in the string, such as: InStr (' String ', ' R ', 1, 1) 3
Lpad: Fills the string with characters to the left to a specified length, such as: Lpad (' Hi ', ', ')--------hi
Rpad: Fills the string with characters to the right of the specified length, such as: Rpad (' Hi ', ', ') Hi--------
Trim: Remove another string in a string, such as: Trim (' S ' from ' ssmith ') mith
Replace: Replaces substrings in another string with one string, such as: replace (' ABC ', ' B ', ' D ') ADC
If you do not know whether the table's field contents are uppercase or lowercase, you can compare them after conversion.
Enter and execute the query:
SQL code
SELECTEmpno, ename, Deptno
fromEmp
WHERELower (ename) = ' Blake ';
SELECT empno, ename, deptno from emp
WHERE lower (ename) = ' Blake ';
The results are:
SQL code EMPNO ename DEPTNO-------------------------------------------------7698 BLAKE 30
EMPNO ename DEPTNO
-------------------------------------------------
7698 BLAKE 30
Description: This query converts the employee name in the table to lowercase, compared to the lowercase Blake.
Date function
Months_between: Returns the two-day period
month, such as: Months_between (' April-November-05 ', ' November-January-01 '), results, 57.7741935
Add_months: Returns a new date that adds the number of months to the date, such as: add_months (' June-February-03 ', 1), results, June-March-03
Add_months (' June-February-03 ',-1), results, June-January-03
Next_day: Returns the new date corresponding to the week after the specified date, such as: Next_day (' June-February-03 ', ' Monday '), results, October-February-03
Last_day: Returns the last day of the month on which the specified date is located, such as: Last_day (' June-February-03 '), results, 2 August-February-03
Round: Rounding the date in the specified format, such as: Round (' 1 March-February -03 ', ' year '), results, January-January-03
Round (To_date (' 1 March-February -03 '), ' MONTH '), results, January-February-03
Round (To_date (' 1 March-February -03 '), ' Day '), results, 1 June-February-03
(rounded by week)
TRUNC: Truncate the date in the specified manner, such as: Trunc (To_date (' June-February -03 '), ' year '), results, January-January-03
Trunc (To_date (' June-February -03 '), ' MONTH '), results, January-February-03
Trunc (To_date (' June-February -03 '), ' Day '), results, February-February-03
(truncated by week)
Return to the last day of February 2003.
Enter and execute the query:
SQL code
SELECTLast_day (' August-February-03 ')
fromDual
SELECT last_day (' August-February -03 ') from dual;
The result returned is:
SQL code Last_day ('----------------2 August-February-03
Last_day ('
----------------
2 August-February-03
Description: The function gives the parameter a day of the month and returns the last day of the month. In this example, the parameter is February 8, 03, and the return date is February 28, 03, which is the last day of the month.
Assume that the current system date is February 6, 2003, and ask for a date of 1000 days.
Enter and execute the query:
SQL code
SELECTsysdate+1000
as"NEW DATE"
fromDual
SELECT sysdate+1000 as "NEW DATE" from dual;
The result returned is:
SQL code NEW
DATE----------------April-November-05
NEW DATE
----------------
Note: The query uses the addition of a date to find a new date after a certain number of days.
Assume that the current system date is February 6, 2003, showing the number of days of employment for 10 employees of the department.
Enter and execute the query:
SQL code
SELECTename, round (sysdate-hiredate) days
fromEmp
WHEREDeptno = 10;
SELECT ename, round (sysdate-hiredate) days
from emp
WHERE deptno = 10;
The result returned is:
Ename days
--------------- ---------------------
CLARK 7913
KING 7752
MILLER 7685
Note: This query uses the subtraction of dates to find the number of days between two dates. The number of days is rounded with the round function.
Conversion functions
Oracle's type conversions are divided into automatic type conversions and coercion type conversions. The commonly used type conversion functions are To_char, to_date, or To_number
To_char: Convert to String type, such as: To_char (1234.5, ' $9999.9 '), Result: $1234.5
To_date: Convert to date type, such as: To_date (' 1980-01-01 ', ' yyyy-mm-dd '), results: January-January-80
To_number: Convert to numeric type, such as: To_number (' 1234.5 '), results: 1234.5
Automatic type conversion
Oracle can automatically make the following conversions according to the circumstances:
* String to numeric value.
* String to date.
* Numeric value to string.
* Date to String.
Here is the training for automatic conversion.
Automatic conversion of character data to numeric values.
Enter and execute the query:
SQL code
SELECT' 12.5 ' +11
fromDual
SELECT ' 12.5 ' +11 from dual;
The results of the execution are:
SQL code ' 12.5 ' +11------------23.5
' 12.5 ' +11
------------
23.5
Note: In this training, because the + operator appears, the arithmetic operation is performed, so the string ' 12.5 ' is automatically converted to a value of 12.5 and then participates in the operation.
Automatic conversion of numeric data to character type.
Execute the following query:
SQL code
SELECT' 12.5 ' | | 11
fromDual
SELECT ' 12.5 ' | | one from dual;
The results are:
SQL code ' 12.5 '------12.511
' 12.5 '
------
12.511
Description: In this training, because of the appearance | | operator, which indicates that the string concatenation operation is performed, the value 11 is automatically converted to the string ' 11 ', and then participates in the operation.
Date type conversions
When you convert a date type to a string, you can display it in a new format.
such as format Yyyy-mm-dd HH24:MI:SS means "year-month-day hour: minutes: SEC". The date type of Oracle is inclusive of time.
The meaning of the main date format character
AM, PM: morning, afternoon, such as:
D: A number of weeks (1~7), such as: 1,2,3,4,5,6,7
DD: The number represents the date of the month (1~31), such as: 1,2,3,..., 31
MM: Two-digit months, such as: 01,02,..., 12
Y, YY, YYY, YYYY years of the following, such as: 3,03,003,2003
RR: Annual conversion to resolve Y2K issues
DY: A shortened weekday name, such as: Mon,tue,fri,...
Day: Full week name, such as: Monday,tuesday,...
MON: Abbreviated month name, such as: Jan,feb,mar,...
MONTH: Full spelling of the month name, such as: January,february,...
HH, Hh12:12 hours (1~12), such as: 1,2,3,..., 12
Hh24:24 hours (0~23), such as: 0,1,2,..., 23
MI: Min (0~59), such as: 0,1,2,..., 59
SS: SEC (0~59), such as: 0,1,2,..., 59
,./-;: punctuation marks displayed as-is
' Text ': the literal in quotation marks is displayed as is, such as: text
Converts a date to a string with time and week and displays it.
Execute the following query:
SQL code
SELECTTo_char (sysdate, ' yyyy-mm-dd HH24:MI:SS AM DY ')
fromDual
SELECT to_char (sysdate, ' yyyy-mm-dd HH24:MI:SS AM DY ') from dual;
The results are:
SQL code To_char (sysdate, ' Yyyy-mm-dd HH24---------------------------------------------------------- 2004-02-07 15:44:48 Afternoon Saturday
To_char (sysdate, ' yyyy-mm-dd HH24
----------------------------------------------------------
2004-02-07 15:44:48 afternoon Saturday
Description: The first parameter in the statement represents the date to be converted, and the second parameter is a format string that represents the converted format and the result type is a string. "YYYY" is a 4-bit year, and "MM" is a two-bit month, "DD" is a two-bit date, "HH24" indicates a 24-hour hour, "MI" indicates a minute, "SS" indicates a second, "AM" indicates the morning or afternoon (in this case, afternoon), and "DY" indicates the week. "-", ":", and spaces are displayed as they are, used to split the date and time. The converted system time is: February 7, 2004 (Saturday) 15:44 P.M. 48 seconds.
You can also display in other formats. In the following query, the date of the Chinese is inserted, which is displayed as if it were a single quotation mark that is different from the outer layer and needs to be enclosed in double quotes.
The date display is converted to the Chinese month date.
Enter and execute the query:
SQL code
SELECTTo_char (sysdate, ' YYYY ' year "MM" month "DD" Day ")
fromDual
SELECT to_char (sysdate, ' YYYY ' year "MM" month "DD" Day ") from dual;
The results of the execution are:
SQL code To_char (sysdat-------------------------November 18, 2003
To_char (sysdat
-------------------------
November 18, 2003
NOTE: Double quotation marks in the Chinese text "year", "Month", "Day" is displayed as is, single quotes as a string of the definition of the mark, different from double quotes, can not be confused.
The case of the time display.
Step 1: Execute the following query:
SQL code
SELECTSysdate,to_char (sysdate, ' YYYYSP ')
fromDual
SELECT Sysdate,to_char (sysdate, ' YYYYSP ') from dual;
The results are:
SQL code sysdate to_char (sysdate, ' YYYYSP ')----------------------------------------------------------- July-February -04 two thousand Four
Sysdate to_char (sysdate, ' YYYYSP ')
-----------------------------------------------------------
July-February -04 two thousand Four
Step 2: Execute the following query:
SQL code
SELECTTo_char (sysdate, ' YYYYSP ')
fromDual
SELECT to_char (sysdate, ' YYYYSP ') from dual;
The results are:
SQL code sysdate to_char (sysdate, ' YYYYSP ')------------------------------------------------------------- Two thousand Four
Sysdate to_char (sysdate, ' YYYYSP ')
-------------------------------------------------------------
Two thousand Four
Numeric conversion characters:
9: For a digit, if it is a positive number, the front is a space, if it is negative, preceded by − number, such as: 9999
0: Represents a digit, in the corresponding position if no number appears 0 0000
,: comma, used as a group separator, such as: 99,999
.: decimal points, separating integers and decimals, such as: 999.9
$:$ currency symbol, such as: $999.9
L: Local currency symbol, such as: L999.99
FM: Remove before and after the space, such as: FM999.99
EEEE: Scientific counting method, such as: 9.9EEEE
S: Negative sign − placed at the beginning, such as: S999.9
Converts a numeric value to a string and displays it in the new format.
Execute the following query:
SQL code
SELECTTo_char (Sal, ' $99,999 ') SALARY
fromEmp
WHEREename = ' SCOTT ';
SELECT To_char (Sal, ' $99,999 ') SALARY from emp
WHERE ename = ' SCOTT ';
The results are:
SQL code SALARY------------$4,000
SALARY
------------
$4,000
Description: "$" in the format string indicates that the conversion result is added before $. "9" indicates a digit, and "99,999" indicates that the result can be displayed as a 5-digit number. "," means adding a comma at the appropriate location. If the actual number of digits is less than 5 digits, only the actual digits are displayed, such as the 4000 actual digits to 4 digits, and only 4 digits are displayed. If the actual number of digits exceeds 5 digits, it is populated with the # number.
Other functions
NVL: null conversion function, such as: NVL (null, ' null '), Result: null
Decode: Implement branching functions, such as: Decode (1, 1, ' Male ', 2, ' female '), results: Male
USERENV: Returns environmental information, such as: Userenv (' LANGUAGE '), results: Simplified Chinese_china. Zhs16gbk
Greatest: Returns the maximum value of the parameter, such as: Greatest (20,35,18,9), results: 35
Least: Returns the minimum value of the parameter, such as: least (20,35,18,9), Result: 9
Conversion of NULL values
If null values are not handled well, there are some problems in the query. The result of arithmetic operations on a null value is null. The most typical example is the addition of the Payroll Sal field and the allowance field Comm when querying the employee table, which can be misleading if the allowance is empty and the result is empty.
Using the NVL function, you can convert null to an actual value. The function determines the contents of the field, if not NULL, returns the original value, or NULL, returns the given value.
The following 3 functions replace the null value of the field with the new content:
NVL (comm, 0): Replaces the empty Comm value with 0.
NVL (HireDate, ' January-January-97 '): Replace the empty hire date with January 1, 1997.
NVL (Job, ' none '): Replace empty positions with "none".
Use the NVL function to convert null values.
Execute the following query:
SQL code
SELECTENAME,NVL (Job, ' none '), NVL (HireDate, ' January-January -97 '), NVL (comm,0)
fromEmp
SELECT ENAME,NVL (Job, ' none '), NVL (HireDate, ' January-January -97 '), NVL (comm,0) from EMP;
The results are:
SQL code ENAME      NVL (JOB, ' N nvl HIREDA NVL (comm,0) ---------------- ------------------- --------- --------- ------------------- SMITH clerk 17-12 Month -80 0 allen salesman 20-2 Month -81 300
ename NVL (JOB, ' N nvl Hireda nvl (comm,0)
---------------------------------------------------------------- --------
SMITH Clerk 1 July-December -80 0
ALLEN salesman 20月-February -81 300
Note: In this example, the empty date will be displayed as "January-January-97", the blank title is "None", and the empty allowance will be displayed as 0.
Max, min value function
Greatest returns the maximum value in the argument list, least returns the minimum value in the argument list.
The arguments for the two functions are an expression list that evaluates to the highest or smallest value by the type of the first expression in the expression list. Comparisons of characters are performed in the order of ASCII code. Returns null if NULL is present in an expression.
Compares the size of the string and returns the maximum value.
Execute the following query:
SQL code
SELECTGreatest (' abc ', ' ABD ', ' abc ', ' ABD ')
fromDual
SELECT Greatest (' abc ', ' ABD ', ' abc ', ' ABD ') from dual;
The results of the execution are:
SQL code GRE------ABD
GRE
------
Abd
Description: In the above four strings, the size relationship is ABD>ABC>ABD>ABC. In the ASCII code table, the characters that are in the back are large, and the lowercase letters are behind the uppercase letters. The comparison principle of strings is to compare the first bit first and, if the same, continue to compare the second bit, and so on until the size relationship occurs.
