Oracle built-in function content collation

--Absolute value
Select ABS ( -100) from dual;

--Take surplus
Select mod (8,3) from dual;

--Rounding, the smallest integer greater than the number (upper value)
Select Ceil (12.0) from dual;
Select Ceil (12.5) from dual;

--rounding, which is less than the maximum number of integers (lower value)
Select Floor (12.5) from dual;

--Rounding, selecting the number of decimal digits to keep
Select Round (12.456,0) from dual;

--intercept decimals, enter the number of decimal places to be retained, maximum length, or the entire
Select Trunc (12.456,1) from dual;

--Intercept length
Select Length (' ABCDEFG ') from dual;
Select Xingm,length (XINGM) from T_hq_ryxx;

--intercept the string, the 1th digit is the intercept position, the positive number starts from the start, the negative number is counted from the right, the second is the intercept length, the intercept position exceeds the character length is empty
Select Xingm,substr (xingm,0,1) from T_hq_ryxx;

--String connection
Select Concat (' AB ', concat (' CD ', ' EFG ')) from dual;
Select ' Ab ' | | ' CD ' | | ' EFG ' from dual;

--Find, you can specify the starting position
Select InStr (' ABCDEFG ', ' d ') from dual;
Select InStr (' ADCDEFG ', ' d ', 1) from dual;
Select InStr (' ABCDEFG ', ' h ') from dual;

--Convert case, capitalize first letter
Select Upper (' Assa '), lower (' ASDF ') from dual;
Select Upper (' Assa '), lower (' ASDF '), Initcap (' This was a test ') from dual;

--Replacement
Select replace (' ABCDEFG ', ' ab ', ' 123 ') from dual;
--update t_hq_ryxx Set xingm = replace (XINGM, ' Amount ', ' a ') where xingm like ' Amount% ';

--padding, no padding with blanks, padding from toward, left padding
Select Rpad (' AA ', 8, ' C ') from dual;
Select Lpad (' AA ', 8) from dual;

--Go to space
Select Trim (' ABCDE ') from dual;
--to the right of the space
Select RTrim (' ABCDE ') from dual;
--Go to the first character
Select Trim (Leading ' a ' from ' AABCDE ') from dual;
--Go to the back of the character
Select trim (Trailing ' a ' from ' Aabcdea ') from dual;
--Go character before and after
Select Trim (both ' a ' from ' Aabacdea ') from dual;

--Current system time
Select Sysdate from dual;
--Modify the current month
Select Add_months (sysdate,-2) from dual;
--Take the last day of the month and modify the number of days
Select Last_day (sysdate) +1 from dual;

--Conversion function
Select CAST (' 123 ' as number) +123 from dual;
Select CAST (123 as VARCHAR2 (3)) + 147 from dual;
Select ' 123 ' +123 from dual;
Select To_char (sysdate, ' yy-dd-mm ') from dual;
Select To_char (sysdate, ' yyyy-dd-mm hh24:mi:ss ') from dual;
--Rounding
Select To_char (123.456, ' 999.9 ') from dual;
--Conversion Date
Select To_date (' 2015-12-23 04:13:25 ', ' yyyy-mm-dd hh24:mi:ss ') +1 from dual;

Update t_hq_ryxx Set Ruzrq = To_date (' 24-10-2015 ', ' dd-mm-yyyy ') where Bianh = 107;

SELECT * from T_hq_ryxx where Ruzrq > To_date (' 2015-10-22 ', ' yyyy-mm-dd ');

--Convert number format
Select To_number (' 123.456 ', ' 999.999 ') from dual;

--null function
--select NVL (nianl,54) from T_hq_ryxx;
--Non-empty replace with 20, null value is 1000
--select Nvl2 (nianl,20,1000) from T_hq_ryxx;
--null value replaced with 0
--select NVL (nianl,0) + Gongz from T_hq_ryxx

--propose content that meets the criteria
SELECT * from T_hq_ryxx where LNNVL (Nianl > 20);

Oracle built-in function content collation