Everyone may encounter a lot of problems that seem to be difficult, especially for novices, today I will summarize it, release it to everyone, I hope to help everyone! Discuss with everyone, and make progress together!
It is not used by Oracle masters.
Oracle internal functions 204. How to get the first character of the string? ASCII (Char) Select ASCII ('Abcde') from Dual; Result: 65
205. How to get a value N specified characters? Chr (n) SELECT CHR (68) from dual; Result: D
206. How to connect two strings? Concat (Char1, char2) select concat ('abc', 'defgh') from dual; Result: 'Abcdefgh'
207. How to replace the values in the column as a string? Decode (char, n1, char1, n2, char2 ...) Select Decode (day, 1, 'sun', 2, 'mon') from dual;
208. INITCAP (Char) The first character of the string char is uppercase, the rest is lowercase. SELECT INITCAP ('Abcde') from DUAL;
209. Length (char) takes a string char length. SELECT Length ('Abcde') from DUAL;
210. Lower (char) all turn all the string char to lowercase. SELECT LOWER ('Abcde') from Dual;
211. LPAD (CHAR1, N, CHAR2) Filled CHAR1 with a string char2, making it a length of N. SELECT LPAD ('Abcdefg', 10'123 ') from Dual; Result:' 123ABCDEFG '212. LTRIM (CHAR, SET) Move the character in the string set from the left side of the string char, until the first one is not a character in the set. SELECT ('cdefg', 'cd') from dual; Result: 'EFG'
213. NLS_INITCAP (CHAR) Take the first character of Charity Char, the rest of the character is lowercase. SELECT NLS_INITCAP ('Abcde') from DUAL
214. NLS_LOWER (Char) All characters included in the string Char. SELECT NLS_LOWER ('AAAA') from Dual;
215. NLS_UPPER (CHAR) All characters included in the string Char. SELECT NLS_UPPER ('AAAA') from Dual;
216. Replace (Char1, Char2, Char3) replaced the column of each column value of CHAR2 with a string Char3, which is placed in Char1. SELECT Replace (EMP_NO, '123', '456') from Dual;
217. RPAD (CHAR1, N, CHAR2) with string Char2 right to right, so that it is N. SELECT RPAD ('234', 8, '0') from DUAL;
218. RTRIM (Char, SET) Removes the character in the string set on the right side of the string, until the last one is not the character in the set. SELECT RTRIM ('Abcde', 'DE') from DUAL
219. Substr (char, m, n) gets N characters starting from M. Double-byte characters, one Chinese character, SELECT SUBSTR ('Abcde', 2, 3) from Dual; 220 Substrb (char, m, n) gets N characters starting from M. Double-byte characters, one Chinese character is two characters. Select Substrb ('Abcde', 2, 3) from DUAL;
221. Translate (Char1, Char2, Char3) Replace the portion of Char2 in Char1 with Char3. Select Translate ('Abcdefgh', 'DE', 'Mn') from DUAL
222. Upper (char) All string char is all uppercase.
223. Add_months (d, n) increases N month to D date. SELECT Add_MontHS (Sysdate, 5) from Dual;
224. Last_day (d) Get the date of the last day of the month containing the D date. SELECT LAST_DAY (SYSDATE) from DUAL
225. Month_between (D1, D2) Get the number of months between two dates. SELECT MONTH_BETWEEN (D1, D2) from DUAL;
226. Next_day (d, char) gets 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;
227. Rount (d, fmt) Get the most intake date rounded by the specified mode FMT. SELECT ROUNT ('2003/09/20', MONTH) from DUAL
228. Sysdate gets the date and time of the current system. SELECT SYSDATE from DUAL
229. TO_CHAR (D, FMT) converts the date D to the FMT string. Select to_char (sysdate, 'yyyy / mm / dd') from DUAL;
230. TO_DATE (CHAR, FMT) Converts the string char in the FMT format. SELECT TO_DATE ('2003/09/20', 'YYYY / MM / DD') from DUAL
231. ABS (N) Gets the absolute value of N. SELECT ABS (-6) from Dual;
232. CEIL (n) Gets the most integer greater than or equal to N. SELECT CEIL (5.6) from Dual;
233. COS (N) Gets the cosine value of N. SELECT COS (1) from dual;
234. sin (n) Gets the sinusoidal value of N. SINET SIN (1) from DUAL;
235. Cosh (n) Gets the hyperbolic cosine value of N. SELECT COSH (1) from DUAL
236. EXP (n) Gets the N power of the E of N. SELECT Exp (1) from dual;
237. Floor (n) Gets minimum integer less than or equal to N. SELECT FLOOR (5.6) from Dual;
238. ln (n) Get N of Natural logs. SELECT LN (1) from Dual;
239. LOG (M, N) Gets the logarithm of the bottom N. SELECT log (2, 8) from DUAL;
240. MOD (m, n) Gets the remainder of M divided by N. SELECT MOD (100, 7) from DUAL;
241. Power (M, N) Gets the N power of M. SELECT POWER (4, 3) from Dual; 242. Round (n, m) Rounded N to M bits after the decimal point. SELECT (78.87653, 2) from Dual;
243. Sign (n) When n <0, get -1; when n> 0, get 1; when n = 0, get 0; select Sign (99) from DUAL;
244. SINH (N) Gets the dual sine value of N. SELECT SINH (1) from Dual;
245. Sort (n) Gets the square root of N, N> = 0 Select Sort (9) from Dual;
246. Tan (n) Gets the nose value of N. SELECT TAN (0) from DUAL;
247. Tanh (n) Gets the hyperbolic nose value of N. SELECT TANH (0) from DUAL;
248. Trunc (n, m) Gets the value of N cut off in the M bit. SELECT TRUNC (7.7788, 2) from DUAL
249. count () calculates the number of records that meet the conditions. SELECT Count (*) from table1 where col1 = 'aaa';
250. max () for the specified sum: SELECT MAX (col1) from table1;
251. min () for the specified sum: SELECT MIN (COL1) from table1;
252. AVG () for the specified sum: SELECT AVG (COL1) from table1;
253. Sum () Calculate the sum. Select SUM (col1) from DUAL;
254. TO_NUMBER (CHAR) Convert characters to values. SELECT TO_NUMBER ('999') from Dual;
Original author: ccbzzp
