Oracle built-in functions (1)

Numeric Functions
SQL code
-- Both input and output are numbers.
-- Abs (x) obtains the absolute value.
Select abs (-1) from dual; -- 1
 
-- Ceil (x) returns the smallest integer greater than or equal to n.
Select ceil (1.4), ceil (-2.5) from dual; -- 2,-2
 
-- Floor (x) returns the maximum integer less than or equal to n.
Select floor (1.4), floor (-2.3) from dual; -- 1,-3
 
-- Mod (a, B) returns the remainder of a/B. If B = 0, return
Select mod (3, 2), mod (3, 0) from dual; -- 1, 3
 
-- Round (a, [B]) Rounding, B is the number of digits that specify rounding,
-- Omit B, to an integer; B is a digit after the decimal point; B is a negative number, B is the first decimal point
Select round (23.78), round (23.78, 1), round (23.78,-1) from dual; -- 24, 23.8, 20
 
-- Trunc (a, [B]) Intercepts numbers. B also indicates the position to be intercepted.
Select trunc (23.78), trunc (23.78, 1), trunc (23.78,-1) from dual; -- 24, 23.7, 20
 
-- Square root
Select sqrt (3), sqrt (9) from dual; -- 1.732, 3
 
-- Power (a, B) power B of
Select power (sqrt (3), 2), power (2, 3) from dual; -- 3, 8
 
-- Sign (number): number> 0, return 1; number <0, return-1; number = 0, return 0.
Select sign (-7), sign (0), sign (7) from dual;
 
-- Greatest (a, B, c, d...) returns the largest
Select greatest (3, 7, 5, 12) from dual; -- 12
-- String comparison, natural sorting, comparison
Select greatest ('about', 'age', 'again ') from dual; -- age
-- If the parameter data type is not available, convert the data to the first parameter type for comparison.
Select greatest ('3', 7,5, 12) from dual; -- 7
 
-- Least (a, B, c, d...) returns the smallest value. The rule is the same as greatest. If one is null, null is returned.
Select least (2,12, 6) from dual; -- 2
Select least ('2', '12', '6'), least (2,34, null) from dual; -- '12', null

 

Character Functions
SQL code
-- Ascii (char) returns the ascii value of char.
Select ascii ('A'), ascii ('D') from dual; -- 97,68
 
-- Chr (n) converts ascii values to characters
Select chr (97), chr (65) from dual; -- a,
 
-- Concat (a, B) connects strings a and B. The function is the same as '| '.
Select concat ('A', 'bb '), 'A' | 'bb' from dual; -- aabb, aabb
 
-- Initcap (char): uppercase of the first letter of each word in the string
Select initcap ('Hello oracle ') from dual; -- hello Oracle
 
-- Length (char) | lengthc (char) returns the length of the string.
Select length ('Hello me') from dual; -- 6
-- Vsize (char) returns the bytes length of the string = lengthb (char)
Select vsize ('hello') from dual;
-- View Character Set
Select * from v $ nls_parameters;
--
Select length ('my'), lengthb ('my'), vsize ('my') from dual; -- 1, 2, 2
Select length ('hello'), vsize ('hello') from dual; -- 5
 
 
-- Instr (char, char1) char1 position in char, you can specify the starting position, and the number of occurrences
Select instr ('hello', 'L') from dual; -- 3, starting from the first character by default and ending the first match
Select instr ('hello', 'l', 1, 2) from dual; -- 4, starting from the first payment and the second position
 
-- Lower (char) string to lowercase
Select lower ('Tom ') from dual; -- TOM
 
-- Upper (char) character conversion to uppercase
Select upper ('Tom ') from dual; -- Tom
 
-- Lpad (char1, n, char2) adds a char2 string to the left of the char1 string until the length of the inserted string reaches n
-- Char2: The default value is space. If char1 is longer than n, n characters on the left of char1 are returned.
Select lpad ('A', 5, 'B') from dual; -- bbbba
Select lpad ('abcdefg', 5, 'H') from dual; -- abcde 5 <length of the original string, directly truncates the original string and returns
 
-- Fill the right end of rpad (char1, n, char2). The rule is to fill the left end of the same face.
Select rpad ('A', 5, 'Big ') from dual; -- abigb
 
 
-- Ltrim (char1, char2) is detected from the left end of char1. If the character at the current position is in char2, remove the character
-- If a character is not character 2, char1 is returned.
Select ltrim ('hello') from dual; -- "hello" removes spaces by default.
Select ltrim ('hello', 'echo ') from dual; -- llo
 
-- Remove characters from the right end of rtrim (char1, char2). Remove characters from the left end of the rule.
Select rtrim ('oracle ', 'Leo') from dual; -- orac
 
-- Substr (char, from, length) truncates a string, from, and length,
Select substr ('oracle ',) from dual; -- or
Select substr ('oracle ', 2, 3) from dual; -- rac
Select substr ('oracle ', 2) from dual; -- racle
Select substr ('oracle ',-2, 3) from dual; -- le, truncate three digits from the right 2nd digits
 
-- Trim (char1 from char2) removes char1 from both sides of char2
Select trim ('A' from 'aba') from dual; -- B
 
-- Translate
/*
Translate (str, str_to_relpace, replace_str)

Str: Operation object
Str_to_replace: Search for characters to be replaced in str
Replace_str: character to be replaced with in the str_to_replace string
Example: translate (str, 'abcd', '123 ')
Replace all 'A' in str with '1', 'B' with '2', 'C' with '3', and 'D' with no replacement character, directly remove.
The replacement of the translate method corresponds to the replaced characters one by one.
*/
Select translate ('oracleoracle ', 'ole', '123') from dual; -- 1rac231rac23
Select translate ('oracleoracle ', 'ole', '12') from dual; -- 1rac21rac2
 
-- Replace
/*
Replace (str, substr1, substr2)
Replace all substr1 substrings in str with substr2
*/
-- Differences between your own experience and translate
-- The effect of a single character is the same as that of a translate character.
Select replace ('1111abc', '1', 'A') from dual; -- AAAAabc
Select replace ('1111abc', '1') from dual; -- abc
-- Multiple characters are different.
Select replace ('1111abc', '11', 'AB') from dual; -- ABABabc
Select translate ('11abc1 ', '11',' AB ') from dual; -- AAAAabcA
 
-- Translate is used to replace one character with one character, and replace is used to replace character substrings.

 

 

Date and Time Functions
SQL code
-- To_date (str, format) converts a string to a date.
/*
Format parameter:
YYYY 4-digit year
Last 3 of YYY, YY, and Y | 2 | 1 is a few
MM Month (01-12)
DD day of month (1-31)
HH = HH12: hour of day (1-12)
HH24: hour of day (0-23)
MI: minute (0-59)
Ss: second (0-59)

*/
Select to_date ('1970-10-01: 22: 35: 24', 'yyyy-MM-DD: HH24: MI: ss') from dual;
 
-- To_char (date, format) converts a date to a string
Select to_char (sysdate, 'Mm-DD-YY ') from dual; -- 08-22-11
 
-- Add_months (date, months) plus the total number of months
Select sysdate, add_months (sysdate, 3), add_months (sysdate,-2) from dual;
 
-- Last_day (date) calculates the last day of the month in which the date is located.
Select last_day (to_date ('1970/08', 'yyyy/mm/dd'), last_day (sysdate) from dual; -- 1988/08-8-31,2011-8-31 22:49:23
 
-- Current time
Select current_date, current_timestamp, sysdate from dual;
/* 23:00:24,-11 11.00.23.765000 PM + 23:00:23 */
 
-- The time zone is useless.
Select dbtimezone from dual;
 
-- Extract information from date
Select extract (year from sysdate), extract (month from sysdate), extract (day from sysdate) from dual;
 
-- Returns the number of months of difference.
Select months_between (sysdate, to_date ('1970-02-15 ', 'yyyy-MM-DD') from dual;

 

Others
SQL code
-- Nvl (exp1, exp2) If exp1 = null, exp2 is returned; otherwise, exp1 is returned.
Select nvl (null, 1), nvl (2, 1) from dual; -- 1, 2
 
-- Nvl2 (exp1, exp2, exp3) returns exp3; exp1 if exp1 = null! = Null, returns exp3
Select nvl2 (null,), nvl2 (, 2) from dual; --
 
-- Coalesce (exp1, exp2, exp3...) returns the first non-null exp value.
Select coalesce (null, null, 2, null, 5) from dual; -- 2
 
-- Cast type Conversion Function
Select cast (2345 as varchar2 (10), cast (sysdate as varchar2 (30) from dual;
 
-- Decode (exp, search1, result1, search2, result2..., default );
/*
Exp: manipulated object
If exp = searchN string, resultN is returned, which is similar to the case statement execution.
Default if not found
*/
Select decode ('000000', '99', '9', '000000', '000000', '000000', '000000', 0) from dual; -- 100

 

This article is from laruence"