Functions in Oracle and their applications

--、、、、、、、、、、、、、、 Common single-line function 、、、、、、、、、、、、、、、、、、、、、、

-------------------commonly used character functions----------------------
--initcap (char): Capitalize first letter
Select Initcap (' Hello ') from dual

--lower (char): Convert to lowercase
Select lower (' Lovey ') from dual

--upper (char): Convert to uppercase
Select Upper (' Love your ') from dual

--ltrim (Char,set): Left Trim
Select LTrim (' abc ', ' abc ') from dual


--rtrim (char,set): Right Trim
Select Rtrim (' abcyou ', ' you ') from dual

--translate (char,from,to): Translate by character
Select Translate (' Jack ', ' ABCD ', ' 1234 ') from dual

---replace (char,search_str,replace_str): string substitution
Select Replace (' Jack and Jue ', ' j ', ' BL ') from dual

--instr (Char,substr[,pos]): Find the first substring position
Select InStr (' Worldwide ', ' d ') from dual

--substr (Char.pos.len): Take substring
Select substr (' ABCDEFG ', 3,2) from dual

--concat (CHAR1,CHAR2): Connection string
Select concat (' Hello ', ' world ') from dual





-------------------commonly used numeric functions-----------------------
--abs (n): Take absolute value
Select ABS ( -14) from dual

--ceil (n): Rounding up
Select Ceil (123.43243155) from dual

--floor (n): Rounding down

Select Floor (123.876) from dual

--sin (n): sine
Select sin (1.23) from dual

--cos (n): cosine
Select cos (0) from dual

--sign (n): Take symbol
Select sign ( -32) from dual

--power (m,n): n Power of M
Select Power (4,2) from dual

--mod (m,n): Take remainder
Select mod (10,4) from dual

--round (m,n): Rounding
Select Round (1254.435421,3) from dual

--trunc (m,n): truncated
Select Trunc (100.256,1) from dual

--SQRT (n): V
Select sqrt (4) from dual






------------------the time function of Orcle--------------

----Months_between: Returns the month gap between two dates
Select Months_between (Date ' 2005-04-05 ', Date ' 2004-04-05 ') from dual

---add_months
Select Add_months (Date ' 2005-04-05 ', 1) from dual

---get current system time
Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

Select To_char (sysdate, ' Q ') from dual--quarterly
Select To_char (sysdate, ' DD ') from dual--date
Select To_char (sysdate, ' IW ') from dual-week. 52 or 53 weeks a year.

--next_day: Returns the new date for the week after the specified date
Select Next_day (Date ' 2015-05-06 ', ' Monday ') from dual


--last_day: Return to the last day of a month
Select Last_day (Date ' 2005-04-05 ') from dual

--round: Rounding a date in the specified format
Select Round (to_date (date ' 2015-02-04 '), ' year ') from dual
Select Round (to_date (date ' 2015-02-04 '), ' month ') from dual
Select Round (to_date (date ' 2015-02-04 '), ' Day ') from dual


----TRUNC: Truncate the date as specified
Select Trunc (to_date (date ' 2015-02-04 '), ' year ') from dual;
Select Trunc (to_date (date ' 2015-02-04 '), ' month ') from dual;
Select Trunc (to_date (date ' 2015-02-04 '), ' Day ') from dual;



---------------commonly used conversion functions-------------------
---to_char: Convert to String type

Select To_char (adopt_time, ' yyyy "years" MM "month" DD "Day") from pet
Select To_char (adopt_time, ' yyyy "-" MM "-" DD ") from pet
Select To_char (adopt_time, ' yyyy "/" MM "/" DD ") from pet
Select To_char (1234.5, ' $9999.9 ') from dual

--to_date: Convert to date type

Select To_date (' 2015-03-04 ', ' YYYY-MM-DD ') from dual

--to_number: Converting to a numeric type
Select To_number (' 1234.6 ') from dual



---------------------Other-------------------------

--NVL (EXP1, EXP2): If the value of EXP1 is null, the value of EXP2 is returned, otherwise the value of EXP1 is returned
Select NVL (name, ' Nameless ') from pet



--NVL2 (EXP1, EXP2, EXP3): If the value of EXP1 is null, the value of EXP2 is returned, otherwise the value of EXP3 is returned
Select NVL2 (name, ' AA ', ' BB ') from pet


---DECODE (value,if1,then1,if2,then2,......, ELSE): If value is IF1, the value of THEN1 is returned.
--if value is IF2, returns the value of Then2, ... otherwise returns the Else value
Select Decode (status,1, ' normal ', 2, ' Disable ') from pet


---、、、、、、、、、、、、、、、、、、、、、 commonly used multi-line functions 、、、、、、、、、、、、、、、、、、、、、
--sum (), AVG (), COUNT (), MAX (), MIN ()

Functions in Oracle and their applications