1. How do I get the ASCII value of the first character of the string?
ASCII (Char)
SELECT ASCII ('Abcde') from Dual;
Result: 65
2. How to get the character specified by value N?
CHR (N)
SELECT CHR (68) from Dual;
Result: D
3. How to connect two strings?
Concat (Char1, Char2)
SELECT Concat ('ABC', 'Defgh') from Dual;
Result: 'Abcdefgh'
4. How do I replace the values in the column as a string?
Decode (CHAR, N1, CHAR1, N2, CHAR2 ...)
Select Decode (day, 1, 'sun', 2, 'mon') from dual;
5. INITCAP (Char)
The first character of the string char is uppercase, the rest is lowercase.
SELECT INITCAP ('Abcde') from Dual;
6. Length (char)
Take a string char length.
SELECT Length ('Abcde') from Dual;
7. Lower (char)
The string char is all lowercase.
SELECT LOWER ('Abcde') from Dual;
8. LPAD (Char1, N, Char2)
Use the character string Char2 to fill the left fill the left fills CHAR1 to make its length N.
SELECT LPAD ('Abcdefg', 10'123 ') from Dual;
Result: '123ABCDEFG'
9. Ltrim (Char, SET)
The characters in the string SET are removed from the left side of the string char, until the first is not a character in the set.
SELECT ('cdefg', 'cd') from dual
Result: 'EFG'
10. NLS_INITCAP (CHAR)
Take the first character of Charity char, the rest of the characters.
SELECT NLS_INITCAP ('Abcde') from Dual;
11. NLS_Lower (char)
All characters including string Char are written.
SELECT NLS_LOWER ('aaaa') from dual;
12. NLS_UPPER (CHAR)
All characters included in the string Char are all capitalized.
Select NLS_UPPER ('AAAA') from Dual;
13. Replace (Char1, Char2, Char3)
With a string char3 instead of each column of each column value of CHAR2, the result is placed in Char1.
SELECT Replace (EMP_NO, '123', '456') from Dual;
14. RPAD (Char1, N, Char2)
Use a string char2 to right to right, and the length is N.
SELECT RPAD ('234', 8, '0') from dual;
15. RTRIM (Char, SET)
Remove the characters in the string set on the right side of the string CHAR until the last one is not a character in the set.
SELECT RTRIM ('Abcde', 'DE') from dual;
16. Substr (Char, M, N)
Get the n characters that start from the string char. Double-byte characters, one Chinese character is a character.
SELECT SUBSTR ('Abcde', 2, 3) from Dual;
17. Substrb (Char, M, N)
Get the n characters starting at the string char. Double-byte characters, one Chinese character is two characters.
SELECT SUBSTRB ('Abcde', 2, 3) from Dual;
18. Translate (char1, char2, char3) replaces the portion of Char2 in char1 with char3.
Select Translate ('Abcdefgh', 'DE', 'Mn') from DUAL;
19. Upper (char)
All string char is all uppercase.
20. Add_MontHS (D, N)
Add N months to the D date.
SELECT Add_MontHS (Sysdate, 5) from Dual;
21. Last_Day (D)
Get the date of the last day of the month containing the D date.
SELECT LAST_DAY (SYSDATE) from DUAL
22. Month_between (D1, D2)
Get the number of months between two dates.
SELECT MONTH_BETWEEN (D1, D2) from DUAL;
23. Next_day (D, Char)
Get the date of the first Sunday named by char than the date D night.
SELECT next_day (to_date "
2003/09/20
'),' Satday ') from dual;
24. Rount (D, FMT)
Get the most intake date rounded by the specified mode FMT.
SELECT ROUNT ('
2003/09/20
', Month from DUAL;
25. sysdate
Get the date and time of the current system.
SELECT SYSDATE from DUAL;
26. To_Char (D, FMT)
Convert the date D to the FMT string.
Select to_char (sysdate, 'yyyy / mm / dd') from dual;
27. TO_DATE (CHAR, FMT)
Convert the string char to the FMT format.
SELECT TO_DATE ('
2003/09/20
',' YYYY / mm / dd ') from dual;
28. ABS (N)
Gets absolute value of N.
SELECT ABS (-6) from Dual;
29. CEIL (N)
A maximum integer greater than or equal to N is obtained.
SELECT CEIL (5.6) from Dual;
30. COS (N)
Got the cosine value of n.
SELECT COS (1) from dual;
31. sin (n)
Gets the sinusoidal value of n.
Select sin (1) from dual;
32. Cosh (N)
Gets the hyperbolic cosine value of N.
SELECT COSH (1) from dual;
33. Exp (n)
Get N power of the E of N.
SELECT Exp (1) from dual;
34. Floor (n)
A minimum integer less than or equal to N is obtained.
Select floor (5.6) from dual;
35. ln (n)
Get N natural logarithm.
Select ln (1) from dual;
36. log (m, n)
The logarithm of the bottom N is obtained.
Select log (2,8) from dual;
37. MOD (M, N)
The amount of M was removed in N.
SELECT MOD (100, 7) from Dual;
38. Power (M, N)
Gets the n power of M.
Select Power (4, 3) from Dual;
39. Round (n, m)
Round N to the m bit after the decimal point.
SELECT (78.87653, 2) from dual;
40. Sign (N)
When n <0, get -1;
When n> 0, get 1;
When n = 0, get 0;
SELECT SIGN (99) from Dual
41. SINH (N)
Gets the dual sine value of n.
SELECT SINH (1) from dual;
42. Sort (n)
Get the square root of N, N> = 0
Select Sort (9) from dual;
43. Tan (N)
Get N of the positive cut value .Select Tan (0) from Dual;
44. Tanh (N)
Gets the hyperbolic positive cut value of N.
SELECT TANH (0) from dual;
45. Trunc (N, M)
Gets the value of n truncated in the M bit.
SELECT TRUNC (7.7788, 2) from Dual;
46. count ()
Calculate the number of records that meet the conditions.
SELECT Count (*) from table1 where col1 = 'aaa';
47. max ()
For the specified columns, the maximum value is required.
SELECT MAX (col1) from table1;
48. min ()
For the specified sum: the minimum value.
SELECT min (col1) from table1;
49. AVG ()
For the specified columns.
SELECT AVG (COL1) from table1;
50. Sum ()
Calculate the sum.
SELECT SUM (col1) from dual;
51. TO_NUMBER (CHAR)
Convert characters to values.
SELECT TO_NUMBER ('999') from Dual;
52. Chartorowid (char)
Converting the CHAR or VARCHAR2 value of the external syntax RowID to the internal binary syntax, the parameter char must be a string of 18 characters that contain the external syntax.
Select Name from bsempms where rowid = chartorowid ('AaaafzaabaaAcp8aao);
Name: Leixue
53. Convert (char, dest_char_set, source_char_set)
Convert converts the characters in the string char from the Source_Char_SET identified by the character set identified by the dest_char_set
Select Convert ('grob', 'US7ASCII', 'WE8HP') 'CONVERSION' from PUBS;
Conversion: Gross
54. HEXTORAW (char)
Converts the CHAR that contains hexadecimal to a RAW value.
INSERT INTO BSEMPMS (Raw_Column) SELECT HEXTORAW ('7D') from test;
55. RawtoHex (RAW)
Converting the RAW value into a CHAR value containing hexadecimal.
Select Rawtohex (Raw_Column) 'Conversion' from bsempms;
Conversion: 7D
56. RowidTochar (RowID)
Convert a ROWID value to a VARCHAR2 data type.
Select Rowid from bsempms where rowidtochar (rowid) Like '% br1aab%;
57. TO_MULTI_BYTE (Char)
Convert the single byte in the char to the equivalent multi-byte character.
SELECT TO_MULTI_BYTE ('asfdfd') from test;
58. to_single_byte (char)
Convert the multi-byte characters in the char.
SELECT TO_SINGLE_BYTE ('asfdfd') from test;
59. Translate Using (Text Using {char_cs | nchar_cs})
Convert text Text to the specified conversion mode into a database character set and national character set.
Where Text is to be converted.
Using char_cs parameter conversion Text is a database character set, and the output data type is VARCHAR2.
Using nchar_cs parameter conversion Text is a database character set, and the output data type is NVARCHAR2.
Create Table Test (CHAR_COL CHAR (20), nchar_col nchar (20)); Insert Into Test Values ('Hi, N'Bye');
SELECT *.
60. Dump (expr, return_format, start_position, length)
Returns a VARCHAR2 value including the data type code, byte length, and the like. The return result is the current database character set, and the data type is returned as a number according to the encoded of the internal data type specified below:
Code data type
0 varchar2
1 Number
8 long
12 Date
23 RAW
24 Long Raw
69 rowid
96 char
106 MSSLabel
The parameter retuen_format specifies the number of returned by the base number below.
Return_Format Result
8 8
10 10
16 16
17 single character
If the parameter return_format is not specified, press the decimation to return.
If the parameter start_position and length are specified, the byte starting from start_position starts to the length of the length will be returned, and the default is to return an integer representation.
SELECT DUMP ('ABC', 1016) from test;
SELECT DUMP (ENAME, 8, 3, 2) 'Example' from Emp where Name = 'CCBZZP';
61. EMPTY_B | CLOB ()
Returns an empty LOB locator, uses in initializing the LOB variable, or ininsert and Update declaration to initialize the LOB column or set it empty.
INSERT INTO TABLE1 VALUES (EMPTY_BLOB ());
Update table1 set clob_col = Empty_blob ();
62. BFileName ('Directory', 'FileName')
Returns a BFile locator, the associated binary LOB physical file is on the server's file system. Directory Directory refers to an alias that actually searches the full name on the server's file system. FileName refers to the file name of the server's file system.
INSERT INTO FILE_TAB VALUES (BFileName ('LOB_DIR', 'Image1.gif');
63. Greatest (expr, expr, ...)
Greatest returns the maximum value of the parameter.
SELECT GRETEST ('Harry', 'Harriot', 'Harold') 'Sample' from Table1;
64. Least (expr, expr, ...)
Least returns the minimum value of the parameter.
Select Least ('Harry', 'Harriot', 'Harold') 'Sample' from Table1;
65. NLS_CHARSET_DECL_LEN (Bytecnt, CSID)
Returns a width of a NCHAR column.
SELECT NLS_CHARSET_DECL_LEN (200, NLS_CHARSET_ID ('JA16EEFDFDF') from table1;
66. NLS_CHARSET_ID (Text)
Returns the number of NLS character set IDs corresponding to NLS character set name.
SELECT NLS_CHARSET_D ('jadfdffdf') from table1;
67. NLS_CHARSET_NAME (N)
Returns the NLS character set name corresponding to the ID number N.
SELECT NLS_CHARSET_NAME (2) from table1;
68. NVL (expr1, expr2)
If EXPR1 is NULL, return EXPR2, otherwise returns expr1.select name, nVL (to_char (comm), 'not application') from table1;
69. UID
Returns the unique identifier to the current database user.
SELECT Uid from Table1;
70. User
Returns the name of the current Oracle user with a varchar2 data type.
SELECT User, Uid from Table1;
71. Userenv (option)
Returns the current session information.
Option = 'isdba' If the current DBA role is current, true, otherwise false.
Option = 'language' Returns the character set of the database.
OPTION = 'sessionID' is the current session identifier.
Option = 'entryID' Returns the auditable session identifier.
OPTION = 'lang' is an ISO news that returns a session language name.
Option = 'instance' returns the current instance.
Select Userenv ('Language') from Dual;
72. vsize (expr)
Returns the number of bytes represented inside the EXPR.
Select Name, vsize (name) from table1;
73. Deref (e)
Returns the object reference of the parameter E.
Select deref (c2) from table1;
74. REFTOHEX (R)
Convert parameter R to 16.
SELECT REFTOHEX (C2) from table1;
75. make_ref (Table, Key, Key ...)
Create a reference to a row in a given view object by using a given key as a primary key.
Create Type T1 As Object (a Number, B Number);
Create Table TB1 (C1 Number, C2 Number, Primary Key (C1, C2));
Create View V1 of T1 with Object OID (A, B) AS SELECT * FROM TB1;
SELECT MAKE_REF (V1, 1, 3) from PUBS;
76. stddev (Distinct | All X)
STDDEV gives a quotation of a set of row values.
SELECT STDDEV (SALARY) AS EXAMPLE from EMPLOYEE
77. Variance (Distinct | All x)
Variance returns the variance of all Value in a group.
SELECT VARIANCE (SALARY) AS EXAMPLE from EMPLOYEE
