Oracle database built-in functions

--oracle built-in functions: single-line functions, aggregate functions
--1. Absolute value, take redundancy, judge the data positive and negative function,
SELECT ABS (+), ABS ( -100), ABS (' + ') from DUAL;
SELECT mod (5,2) mod (8/3,5), mod (' Ten ', 5), mod ( -10,6), mod (1,0) from DUAL;
SELECT sign (' 9 '), sign ( -9), sign (0.00), sign ( -2* ' 9 ") from DUAL;

--Trigonometric functions
SELECT cos (3.1415926), cos (' 3.1415926 ') from DUAL;
ACOS (n) returns the inverse cosine value of n
COSH (n) returns the hyperbolic cosine of n
SIN (n) returns the positive value of n
SINH (n) returns the hyperbolic positive value of n
ASIN (n) returns the inverse value of n
TAN (n) returns the tangent value of n
TANH (n) returns the hyperbolic tangent value of n
ATAN (n) returns the tangent value of n

--Returns a function with the specified numeric value as a quasi-integer
SELECT cell, cell (' 10.5 '), cell ( -10.2) from DUAL;
SELECT floor (+), floor (' 10.5 '), floor ( -10.2) from DUAL;

--Exponential logarithm
SELECT SQRT (+), SQRT (' 53.9 ') from DUAL;
SELECT Power (5,2), Power (' 5 ', 2), Power (5.5,2.5), from DUAL;
SELECT log (10,100), log (10.5, ' n '), POWER (10,2) from DUAL;

--Rounding
SELECT ROUND (100.23456,4), ROUND (100.23456,2.56), ROUND (155.23456,-2) from DUAL;

--ascii code and character conversion function
SELECT CHR (65) | | CHR (66) | | CHR (ABC,CHR) (54678) from DUAL;
SELECT ASCII (' Ming '), ASCII (' ADB '), ASCII (' ABC ') from DUAL;

--Letter case conversion function with sorting parameters
--nls_initcap (PARAM) converts the first letter of a specified parameter to uppercase
--nls_upper (PARAM) turns the specified argument into uppercase
--nls_lower (PARAM) changes the specified parameter to lowercase
SELECT Nls_initcap (' A test '), Nls_initcap (' My Test ', ' nls_sort=schinese_stroke_m ') from dual;
SELECT Nls_initcap (' This was a test '), Nls_initcap (' My Test ', ' Nls_sort=schinese_pinyin_m ') from dual;
SELECT nls_initcap (' A test '), Nls_initcap (' MY test ', ' nls_sort=schinese_stroke_m ') from dual;

--ordering functions for the specified parameters
SELECT * from ProductInfo ORDER by Nlssort (PROCEDURE, ' nls_sort=schinese_pinyin_m ')

Select trim (trailing ' t ' from ' test '), trim (' test ') from dual;
Select RTrim (' test*ffs ', ' fs*t ') from dual;
Select LTrim (' Ftest ', ' f '), Length (LTrim (' ftest ', ' f ')) from dual;

--Character set name and ID conversion function
Select nls_charset_id (' Us7ascii ') from dual;
Select Nls_charset_name (1) from dual;

--Date function
Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;
select Systimestamp from Daul;
select Dbtimezone from dual;--Get the database time zone function
Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss ') One,to_char (New_time (sysdate, ' PDT ', ' est '), ' Yyyy-mm-dd Hh24:mi: SS ') the time conversion function of the From dual;--time zone
--String to ASCII type string function
Select Asciistr (' This is the test! ') from dual;
--Binary conversion to decimal
Select Bin_to_num (1), Bin_to_num (1,0,0), Bin_to_num (1,1,1) from dual;
--Data type conversion function
cast ()
--String conversion rowid function
Select Chartorowid (' AAAABBBCCC ') from dual;
Select Rowidtochar (ROWID) from dual;

--string conversion function between character sets
Select CONVERT (' Test ', ' us7ascii ', ' ZHS16GBK ') from dual;

Oracle database built-in functions