Oracle + common SQL + notes

Single-record functions in SQL

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 ZERO SPACE
------------------------------------
65 97 48 32

2. CHR
Returns the corresponding characters;
SQL> Select Chr (54740) Zhao, Chr (65) Chr65 From Dual;
ZH C
---
Zhao
3. CONCAT
Connect 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 Name, Length (Name), Addr, Length (Addr), Sal, Length (To_char (Sal) From. Nchar_tst;
Name length (NAME) addr length (ADDR) sal length (TO_CHAR (SAL ))
---------------------------------------------------------------------------
Gao Qian Jing 3 Beijing haiding District 6 9999.99 7
 
7. LOWER
Returns a 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 ('abbccdd') Upper From Dual;
UPPER
--------
AABBCCDD
 
9. RPAD and LPAD (paste characters)
Pad the character on the right of the column
LPAD paste characters on 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 deletes the string on the left
RTRIM deletes the string that appears 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.
String to be replaced by S1
String to be replaced by S2
SQL> Select Replace ('He Love you', 'hes', 'I') From Dual;
REPLACE ('heloveyou', 'hes', 'I ')
------------------------------
I Love You

13. SOUNDEX
Returns a string with the same pronunciation as a given string.
SQL> Create Table Table1 (Xm Varchar (8 ));
SQL> Insert Into Table1 Values ('weate ');
SQL> Insert Into Table1 Values ('wether ');
SQL> Insert Into Table1 Values ('gao ');
SQL> Select Xm From Table1 Where Soundex (Xm) = Soundex ('weate ');
XM
--------
Weather
Wether

14. TRIM ('s 'From 'string ')
LEADING
TRAILING
If this parameter is not specified, the space character is used by default.
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 to 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)
---------------------------
1 0 2

27. POWER
Returns the N2 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 1 if the number N is greater than 0,-1 if the value is less than 0, and 0 if the value is 0.
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 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
Truncate 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 ('000000', 'yyymmm'), 2), 'yyymmm') From Dual;
TO_CHA
------
200002
SQL> Select To_char (Add_months (To_date ('000000', 'yyymmm'),-2), 'yyymmm') From Dual;
TO_CHA
------
199910

37. LAST_DAY
Returns the last day of the date.
SQL> Select To_char (Sysdate, 'yyyy. Mm. dd'), To_char (Sysdate) + 1, 'yyyy. Mm. dd') From Dual;
TO_CHAR (SY TO_CHAR (S
--------------------
2004.05.09 2004.05.10
SQL> Select Last_day (Sysdate) From Dual;
LAST_DAY (S
----------
September 31-04

38. MONTHS_BETWEEN (Date2, Date1)
Given the month of the Date2-Date1
SQL> Select Months_between ('19-December-1999 ', '19-March-1999') Mon_between From Dual;
MON_BETWEEN
-----------
9
SQL> Selectmonths_between (To_date ('192. 100', 'yyyy. Mm. dd'), To_date ('192. 100', 'yyyy. 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
2 (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 ')
Returns the Date of the week and the Date of the next week after week X.
SQL> Select Next_day ('18-May-2001 ', 'Friday') Next_day From Dual;
NEXT_DAY
----------
25-5-01
 
41. SYSDATE
Used to obtain the current date of the system
SQL> Select To_char (Sysdate, 'dd-Mm-Yyyy Day') From Dual;
TO_CHAR (SYSDATE ,'
-----------------
09-05-2004 Sunday
Trunc (Date, Fmt) truncates the Date according to the given requirements. If Fmt = 'mi ', it indicates that the minute is retained and the second is truncated.
SQL> Select To_char (Trunc (Sysdate, 'hh'), 'yyyy. Mm. Dd Hh24: Mi: ss') Hh,
2 To_char (Trunc (Sysdate, 'mi'), 'yyyy. Mm. Dd Hh24: Mi: ss') Hhmm From Dual;
HH HHMM
--------------------------------------
2004.05.09 11:00:00 2004.05.09 11:17: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 hexadecimal 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 ')
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
Converts a given character to a number.
SQL> Select To_number ('20140901') Year From Dual;
YEAR
---------
1999

1 2 3 4 Next Page