Oracle function sorting (1)

1. ASCII: returns the decimal number corresponding to the specified character;
SQL> select ASCII ('A') A, ASCII ('A') A, ASCII (0) zero, ASCII ('') space from dual;
A A zero space
------------------------------------
65 97 48

2. CHR: returns an integer and returns the corresponding character;
SQL> select CHR (54740) Zhao, CHR (65) chr65 from dual;
Zh C
---
Zhao
3. Concat: connects two strings;
SQL> select Concat ('010-', '000000') |' to 23' Gao Qian competing phone number from dual;
Gao Qian's phone number
----------------
010-88888888 to 23

4. initcap: returns a string and converts the first letter of the string to uppercase;
SQL> select initcap ('Smith ') upp from dual;
UPP
-----
Smith

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

6. Length: returns the length of the string;
SQL> select length ('Smith ') length from dual;
Length
-----
5

7. Lower: returns the string and lowercase letters of all characters.
SQL> select lower ('abbccdd') aabbccdd from dual;
Aabbccdd
--------
Aabbccdd

8. Upper: returns a string and upper-case all characters.
SQL> select upper (aabbccdd) Upper from dual;
Upper
--------
Aabbccdd

9. rpad and lpad: paste the character lpad on the right of the column to the left of the column.
SQL> select lpad (rpad ('gao', 10, '*'), 17, '*') from dual;
Lpad (rpad (Gao, 1
-----------------
* ******* Gao *******
If the character is not enough, use * to fill it up.

10. ltrim and rtrim: ltrim Delete the string spaces on the left and rtrim Delete the string spaces on the right.
SQL> select ltrim (rtrim ('gao Qian jing') from dual;
Ltrim (rtrim (
-------------
Gao Qian Jing

11. substr (string, start, count): substring, starting from start, count
SQL> select substr ('123', 13088888888) from dual;
Substr (
--------
08888888

12. Replace (string, S1, S2): string the character or variable to be replaced, the string to be replaced by S1; the string to be replaced by S2
SQL> select Replace ('He love you', 'hes', 'I') from dual;
Replace (H
----------
I love you

13. soundex: returns a string with the same pronunciation as the given string.
SQL> Create Table Table1 (XM varchar (8 ));
SQL> insert into Table1 values (weather );
SQL> insert into Table1 values (wether );
SQL> insert into Table1 values (GAO );
SQL> select XM from Table1 where soundex (XM) = soundex (weather );
XM
--------
Weather
Wether

14. Trim (s from string): Leading removes the previous character; Trailing removes the subsequent character. If this parameter is not specified, it is a space character by default.
SQL> select trim ('s 'from 'ass') trim from dual;
Trim
--------
A

15. Abs: returns the absolute value of a specified value.
SQL> select ABS (100), ABS (-100) from dual;
ABS (100) ABS (-100)
------------------
100 100

16. ACOs: returns the arc cosine value.
SQL> select ACOs (-1) from dual;
ACOs (-1)
---------
3.1415927

17. asin: returns the arcsin value.
SQL & gt; select asin (0.5) from dual;
Asin (0.5)
---------
. 52359878

18. atan: returns the arc tangent of a number.
SQL> select atan (1) from dual;
Atan (1)
---------
. 78539816

19. Ceil: returns the smallest integer greater than or equal to the given number.
SQL> select Ceil (3.1415927) from dual;
Ceil (3.1415927)
---------------
4

20. Cos: returns the cosine of a given number.
SQL> select cos (-3.1415927) from dual;
Cos (-1, 3.1415927)
---------------
-1
21. Cosh: returns the arc cosine of a number.
SQL> select cosh (20) from dual;
Cosh (20)
---------
242582598

22. Exp: returns the n root of the number E.
SQL> select exp (2), exp (1) from dual;
Exp (2) exp (1)
------------------
7.3890561 2.7182818

23. Floor: returns an integer for a given number.
SQL> select floor (2345.67) from dual;
Floor (2345.67)
--------------
2345

24. ln: returns the logarithm of a number.
SQL> select ln (1), Ln (2), Ln (2.7182818) from dual;
Ln (1) ln (2) ln (1, 2.7182818)
-------------------------------
0. 69314718. 99999999

25. Log (N1, N2): returns the base N2 logarithm of N1.
SQL> Select log (2, 1), log (2, 4) from dual;
Log (2, 1) log (2, 4)
------------------
0 2

26. Mod (N1, N2): returns the remainder of N1 divided by N2.
SQL> select Mod (10, 3), MOD (3, 3), MOD (2, 3) from dual;
MoD () mod)
---------------------------
1 0 2

27. Power: return the N2 power root of N1
SQL> select power (2, 10), power (3, 3) from dual;
Power (2, 10) power (3, 3)
---------------------
1024 27

28. Round and trunc: round according to the specified precision
SQL> select round (55.5), round (-55.4), trunc (55.5), trunc (-55.5) from dual;
Round (55.5) round (-55.4) trunc (55.5) trunc (-55.5)
----------------------------------------------
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.
SQL> select sign (123), sign (-100), sign (0) from dual;
Sign (123) sign (-100) Sign (0)
----------------------------
1-1 0

30. Sin: returns the sine of a number.
SQL> select sin (1.57079) from dual;
Sin (1.57079)
------------
1
31. Sigh: returns the hyperbolic sine value.
SQL> select sin (20), sinh (20) from dual;
Sin (20) sinh (20)
------------------
.. 91294525 242582598

32. SQRT: returns the root of the number n.
SQL> select SQRT (64), SQRT (10) from dual;
SQRT (64) SQRT (10)
------------------
8 3.1622777

33. Tan: returns the tangent of a number.
SQL> select Tan (20), Tan (10) from dual;
Tan (20) Tan (10)
------------------
2.2371609. 64836083

34. Tanh: returns the hyperbolic tangent of number n.
SQL> select Tanh (20), Tan (20) from dual;
Tanh (20) Tan (20)
------------------
1 2.2371609

35. trunc: Extract A number based on the specified precision
SQL> select trunc (124.1666,-2) trunc1, trunc (124.16666, 2) from dual;
Trunc1 trunc (124.16666, 2)
---------------------------
100 124.16

36. add_months: add or subtract a month
SQL> select to_char (add_months (to_date (199912, yyyymm), 2), yyyymm) from dual;
To_cha
------
200002
SQL> select to_char (add_months (to_date (199912, yyyymm),-2), yyyymm) from dual;
To_cha
------
199910

37. last_day: return the last day of the date month
SQL> select to_char (sysdate, yyyy. Mm. dd), to_char (sysdate) + 1, yyyy. Mm. DD) from dual;
To_char (SY to_char (S
--------------------
2009-11-17 2009-11-18
SQL> select last_day (sysdate) from dual;
Last_day (S
----------
2009-11-30

38. months_between (date2, date1): Give the month of the date2-date1
SQL> select months_between ('19-December-1999 ', '19-March-1999') mon_between from dual;
Mon_between
-----------
9
SQL> select months_between (to_date ('2017. 05.20 ', 'yyyy. mm. dd'), to_date ('2017. 05.20 ', 'yyyy. mm. dd') mon_betw from dual;
Mon_betw
---------
-60

39. new_time (date, this, that): returns the date and time in this time zone = Other Time Zone
SQL> 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;
Bj_time los_angles
--------------------------------------
2004.05.09 11:05:32 2004.05.09 18:05:32

40. next_day (date, day): Calculate the date of the next week after the given date and week x
SQL> select next_day (to_date ('1970-11-17 ', 'yyyy-mm-dd'), 'weday') next_day from dual;
Next_day
----------
2009-11-18

41. sysdate: used to obtain the current date of the system
SQL> select to_char (sysdate, 'yyyy-mm-dd Day') from dual;
To_char (sysdate,
-----------------
2009-11-17 Tuesday
Trunc (date, FMT): truncates a date based on the given requirements. If FMT = mi, the minute is retained and the second is truncated.
SQL> 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;
HH hhmm
--------------------------------------
2009.11.17 10:00:00 2009.11.17 10:58:00

42. chartorowid: Convert character data type to rowid type
SQL> select rowid, rowidtochar (rowid), ename from Scott. EMP;
Rowid rowidtochar (rowid) ename
----------------------------------------------
Aaaafkaacaaaaeqaaa Smith
Aaaafkaacaaaaeqaab Allen
Aaaafkaacaaaaeqaac ward
Aaaafkaacaaaaeqaad Jones

43. Convert (C, dset, sset): Convert the source string sset from one language character set to another destination dset Character Set
SQL> select convert ('strutz', 'we8hp ', 'f7dec') Conversion from dual;
Conver
------
Strutz

44. hextoraw: converts a hex string to a binary string.

45. rawtohext: converts a binary string to a hexadecimal string.

46. rowidtochar: Convert the rowid data type to the character type

47. to_char (date, format): Convert the date type to the string type by format
SQL> select to_char (sysdate, yyyy/mm/DD hh24: MI: SS) from dual;
To_char (sysdate, YY
-------------------
2004/05/09 21:14:41

48. to_date (string, format): converts a string to a date in Oracle.

49. to_multi_byte: converts a single-byte character in a string to a multi-byte character.
SQL> select to_multi_byte (high) from dual;
To
--
High

50. to_number: Convert the given characters to numbers.
SQL> select to_number (1999) year from dual;
Year
---------
1999