Summary of 50 Oracle Functions

-- 1. ASCII returns the decimal number corresponding to the specified character;
Select ASCII ('A') A, ASCII ('A') A, ASCII ('0') zero, ASCII ('') Space
From dual;
-- Return Value: 65 97 48 32

-- 2. the CHR returns an integer and the corresponding character;
Select CHR (54740) Zhao, CHR (65) chr65 from dual;
-- Return: Zhao

-- 3. Concat connects two strings;
Select Concat ('010-', '000000') |' to 23' Gao Qian's competing phone number from dual;
-- Return Value: 010-88888888 to 23

-- 4. initcap returns the string and converts the first letter of the string to uppercase;
Select initcap ('Smith ') upp from dual;
-- Return: Smith

-- 5. instr (C1, C2, I, j) searches for the specified character in a string and returns the location where the specified character is found;
-- String searched by C1
-- C2 string to be searched
-- I indicates the start position of the search. The default value is 1.
-- Where J appears. The default value is 1.
Select instr ('oracle traning', 'A', 1, 2) instring from dual;
-- Return Value: 9

-- 6. Length: returns the length of the string;
Select name, length (name), ADDR, length (ADDR), Sal, length (to_char (SAL ))
From Gao. nchar_tst;
-- Return: Gao Qian Jing 3 Beijing haiding District 6 9999.99 7

-- 7. lower returns a string and lowercase all characters
Select lower ('abbccdd') aabbccdd from dual;
-- Return Value: aabbccdd

-- 8. Upper returns a string and uppercase all characters.
Select upper ('abbccdd') Upper from dual;
-- Return Value: aabbccdd

-- 9. rpad and lpad (paste character): paste character lpad on the right of the column to paste character on the left of the column
Select lpad (rpad ('gao', 10, '*'), 17, '*') from dual;
-- Return Value: ********* Gao *******

-- 10. ltrim and rtrimltrim Delete the string on the left. rtrim Delete the string on the right.
Select ltrim (rtrim ('gao Qian jing', ''),'') from dual;
-- Return Value: Gao Qian Jing

-- 11. substr (string, start, count): substring, starting from start, count
Select substr ('20140901', 3, 8) from dual;
-- Return Value: 08888888

-- 12. Replace ('string', 's1', 's2 ')
-- String the character or variable to be replaced
-- S1 string to be replaced
-- String to be replaced by S2
Select Replace ('He love you', 'hes', 'I') from dual;
-- Return: I love you

-- 13. soundex returns a string with the same pronunciation as the given string.
Create Table Table1 (XM varchar (8 ));
Insert into Table1 values ('weate ');
Insert into Table1 values ('wether ');
Insert into Table1 values ('gao ');
Select XM from Table1 where soundex (XM) = soundex ('weate ');
-- Returned result: Weather wether

-- 14. Trim a character. If this parameter is not specified, spaces are deleted by default.
Select trim ('s 'from 'string') from dual;
Select trim ('string') from dual;
Select ltrim ('string') from dual;
Select rtrim ('string') from dual;

-- 15. Abs returns the absolute value of the specified value.
Select ABS (100), ABS (-100) from dual;
-- Return result: 100 100

-- 16. ACOs returns the arc cosine value.
Select ACOs (-1) from dual;
-- Return result: 3.1415927

-- 17. asin returns the arcsin value.
Select asin (0, 0.5) from dual;
-- Return result: 0.523598775598299

-- 18. atan returns the arc tangent of a number.
Select atan (1) from dual;
-- Return result: 0.785398163397448
 
-- 19. Ceil returns the smallest integer greater than or equal to the given number.
Select Ceil (1, 3.1415927) from dual;
-- Return result: 4

-- 20. Cos returns the cosine of a given number.
Select cos (-3.1415927) from dual;
-- Return result:-0.999999999999999

-- 21. cosh returns the arc cosine of a number.
Select cosh (20) from dual;
-- Return result: 242582597.704895

-- 22. Exp returns the n root of the number E.
Select exp (2), exp (1) from dual;
-- Return result: 7.38905609893065 2.71828182845905

-- 23. Floor: returns an integer for a given number.
Select floor (2345.67) from dual;
-- Return result: 2345

-- 24. ln returns the logarithm of a number.
Select ln (1), Ln (2), Ln (2.7182818) from dual;
-- Return result: 0 0.693147180559945 0.999999989530502

-- 25.log( N1, N2) returns a base N2 logarithm of N1.
Select log (2, 1), log (2, 4) from dual;
-- Return result: 0 2

-- 26.mod( N1, N2) returns the remainder of N1 divided by N2.
Select Mod (), MOD (), MOD () from dual;
-- Return result: 1 0 2

-- 27. Power returns the N2 root of N1.
Select power (2, 10), power (3, 3) from dual;
-- Return result: 1024 27

-- 28. Round and trunc are rounded according to the specified precision.
Select round (55.5), round (-55.4), trunc (55.5), trunc (-55.5) from dual;
-- Return result: 56-55 55-55

-- 29. Sign: returns the number n. If it is greater than 0, 1 is returned. If it is less than 0,-1 is returned. If it is equal to 0, 0 is returned.
Select sign (123), sign (-100), sign (0) from dual;
-- Return result: 1-1 0

-- 30. Sin returns the sine of a number.
Select sin (0, 1.57079) from dual;
-- Return result: 0.999999999979986

-- 31. Sigh returns the hyperbolic sine value.
Select sin (20), sinh (20) from dual;
-- Return result: 0.912945250727628 242582597.704895

-- 32. SQRT returns the root of the number n
Select SQRT (64), SQRT (10) from dual;
-- Return result: 8 3.16227766016838

-- 33. Tan returns the tangent of a number.
Select Tan (20), Tan (10) from dual;
-- Return result: 2.23716094422474 0.648360827459087

-- 34. Tanh returns the hyperbolic tangent of number n.
Select Tanh (20), Tan (20) from dual;
-- Return result: 1 2.23716094422474

-- 35. trunc intercepts a number based on the specified precision
Select trunc (124.1666,-2) trunc1, trunc (124.16666, 2) from dual;
-- Return result: 100 124.16

-- 36. add_months add or subtract a month
Select to_char (add_months (to_date ('201312', 'yyyymmm'), 2), 'yyyymmm') from dual;
-- Return result: 200002
Select to_char (add_months (to_date ('201312', 'yyyymmm'),-2), 'yyyymmm') from dual;
-- Return result: 199910

-- 37. last_day returns the last day of the date
Select to_char (sysdate, 'yyyy. Mm. dd'), to_char (sysdate) + 1, 'yyyy. Mm. dd') from dual;
-- 2010.09.14 2010.09.15
Select last_day (sysdate) from dual;
-- 04:48:10

-- 38. months_between (date2, date1) gives the month of the date2-date1
Select months_between ('19-December-1999 ', '19-March-1999') mon_between from dual;
-- Return result: 9
Select months_between (to_date ('2017. 05.20 ', 'yyyy. mm. dd'), to_date ('2017. 05.20 ', 'yyyy. mm. dd') mon_betw from dual;
-- Return result:-60

-- 39. new_time (date, 'this', 'that ') indicates the date and time in this time zone = Other time zone.
Select to_char (sysdate, 'yyyy. mm. dd hh24: MI: ss') bj_time, to_char (new_time (sysdate, 'pdt ', 'gmt'), 'yyyy. mm. dd hh24: MI: ss') los_angles from dual;
-- Return result: 2010.09.14 16:53:25 2010.09.14 23:53:25

-- 40. next_day (date, 'day') calculates the date of the next week after date and X of the week.
Select next_day ('18-May-2001 ', 'Friday') next_day from dual;

-- 41. sysdate is used to obtain the current date of the system.
Select to_char (sysdate, 'dd-mm-yyyy Day') from dual;
-- Return result: Tuesday 14-09-2010

-- Trunc (date, FMT) truncates the date according to the given requirements. If FMT = 'mi ', the minute is retained and the second is truncated.
Select to_char (trunc (sysdate, 'hh'), 'yyyy. Mm. dd hh24: MI: ss') HH,
To_char (trunc (sysdate, 'mi'), 'yyyy. Mm. dd hh24: MI: ss') hhmm
From dual;
-- Return result: 2010.09.14 16:00:00 2010.09.14 16:56:00

-- 42. chartorowid converts the character data type to the rowid type
Select rowid, rowidtochar (rowid), ename from Scott. EMP;

-- 43. Convert (C, dset, sset) converts the source string sset from one language character set to another destination dset Character Set
Select convert ('strutz', 'we8hp ', 'f7dec') "Conversion" from dual;
-- Return result: strutz

-- 44. to_char (date, 'format ')
Select to_char (sysdate, 'yyyy/MM/DD hh24: MI: ss') from dual;
-- Return result: 2010/09/14 17:26:24

-- 45. to_date (string, 'format') converts a string to a date in Oracle.
Select to_date ('1970-05-10 ', 'yyyy-mm-dd') from dual;
-- Return Value: 2010-5-10

-- 46. to_multi_byte converts a single-byte character in a string to a multi-byte character.
Select to_multi_byte ('high') from dual;

-- 47. to_number converts the given character to a number
Select to_number ('20140901') year from dual;
-- Return Value: 1999

-- 48. Greatest returns the maximum value in a group of expressions, that is, compare the character encoding size.
Select greatest ('A', 'AB', 'ac') from dual;
-- Return Value: AC
Select greatest ('Ah', 'an', 'day') from dual;
-- Return: Day

-- 49. Least returns the minimum value in a group of expressions.
Select least ('Ah', 'an', 'day') from dual;
-- Return: Ah

-- 50. The user returns the name of the current user.
Select User from dual;
-- Return: Scott