Oracle Common Fool question 1000 question (vi)

Source: Internet
Author: User
Tags abs date chr cos integer sin sort square root
Oracle
Oracle Common Fool question 1000 question (vi)

Author: ccbzzp

There are a lot of things that can come up to you when you're using Oracle, especially for beginners, today I simply end it, and I want to give it to everyone and hope it helps! And we explore together, together to move!

It is not necessary for Oracle masters to be seen.



Oracle Internal functions
204. How do I get the ASCII value of the first character of the string?
ASCII (CHAR)
SELECT ASCII (' ABCDE ') from DUAL;
Results: 65

205. How do I get the number value n specified characters?
CHR (N)
SELECT CHR from DUAL;
Outcome: D

206. How to connect two strings?
CONCAT (CHAR1,CHAR2)
SELECT CONCAT (' ABC ', ' defgh ') from DUAL;
Outcome: ' ABCDEFGH '

207. How do I replace numbers in a column with strings?
DECODE (Char,n1,char1,n2,char2 ...)
SELECT DECODE (day,1, ' SUN ', 2, ' MON ') from DUAL;

Initcap (CHAR)
Writes the first character of the string char, and the rest is small.
SELECT initcap (' ABCDE ') from DUAL;

209. LENGTH (CHAR)
Takes the length of a string char.
SELECT LENGTH (' ABCDE ') from DUAL;

210. LOWER (CHAR)
Changes the string char all to a small write.
SELECT LOWER (' ABCDE ') from DUAL;

211. Lpad (CHAR1,N,CHAR2)
Fill the CHAR1 with the character left-aligns included with the string CHAR2 to make it grow N.
SELECT lpad (' ABCDEFG ', ' 123 ') from DUAL;
Outcome: ' 123ABCDEFG '

212. LTRIM (char,set)
Removes the character from the string set from the left edge of the string char until the first is not a character in set.
SELECT (' CDEFG ', ' CD ') from DUAL;
Outcome: ' EFG '

213. Nls_initcap (CHAR)
The first character of the character Char is written in large, and the remaining characters are written in small letters.
SELECT nls_initcap (' ABCDE ') from DUAL;

214. Nls_lower (CHAR)
Writes the characters that are included in the string char all small.
SELECT nls_lower (' AAAA ') from DUAL;

215. Nls_upper (CHAR)
Writes all the characters that are included in the string char.
SELECT nls_upper (' AAAA ') from DUAL;

216. REPLACE (CHAR1,CHAR2,CHAR3)
Use string CHAR3 instead of each column whose column value is CHAR2, the result is placed in the CHAR1.
SELECT REPLACE (emp_no, ' 123 ', ' 456 ') from DUAL;

217. Rpad (CHAR1,N,CHAR2)
CHAR2 the string with the string CHAR1 to make it grow N.
SELECT rpad (' 234 ', 8, ' 0 ') from DUAL;

218. RTRIM (Char,set)
Removes the character from the string set in the right side of the string char until the last one is not a character in set.
SELECT RTRIM (' ABCDE ', ' DE ') from DUAL;

219. SUBSTR (Char,m,n)
Gets the n characters from which the string char starts from M. Two-character, one-character.
SELECT SUBSTR (' ABCDE ', 2,3) from DUAL;

SUBSTRB (Char,m,n)
Gets the n characters from which the string char starts from M. A pair of characters, a two-character word.
SELECT substrb (' ABCDE ', 2,3) from DUAL;

221. TRANSLATE (CHAR1,CHAR2,CHAR3)
Replace the CHAR2 part of the CHAR1 with CHAR3.
SELECT TRANSLATE (' abcdefgh ', ' DE ', ' MN ') from DUAL;

222. UPPER (CHAR)
Writes the string char all to large.

223. Add_months (D,n)
Increase n month to D date.
SELECT add_months (sysdate,5) from DUAL;

224. Last_day (D)
Gets the date of the last day of the month that contains the D date.
SELECT Last_day (sysdate) from DUAL;

Month_between (D1,D2)
Get the number of months between the two dates.
SELECT Month_between (D1,D2) from DUAL;

Next_day (D,char)
Get the date of the first Sunday that is named by Char, which is later than the date d.
SELECT Next_day (to_date (' 2003/09/20 '), ' Satday ') from DUAL;

Rount (D,FMT)
Gets the most advanced date rounded to FMT in the specified mode.
SELECT rount (' 2003/09/20 ', MONTH) from DUAL;

228. Sysdate
Get the date and time of the previous system.
SELECT sysdate from DUAL;

229. To_char (D,FMT)
Convert Date D to FMT string.
SELECT to_char (sysdate, ' Yyyy/mm/dd ') from DUAL;

230. To_date (CHAR,FMT)
Convert string char to date in 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 maximum integer greater than or equal to N.
SELECT ceil (5.6) from DUAL;

233. COS (N)
Gets the cosine of N.
SELECT COS (1) from DUAL;

234. SIN (N)
Gets the sine value of N.
SELECT SIN (1) from DUAL;

235. COSH (N)
Gets the hyperbolic cosine of N.
SELECT COSH (1) from DUAL;

236. EXP (N)
N Times 冪 of E of N is obtained.
SELECT EXP (1) from DUAL;

237. FLOOR (N)
Gets the smallest integer less than or equal to N.
SELECT FLOOR (5.6) from DUAL;

238. LN (N)
Gets the natural numbers of N.
SELECT LN (1) from DUAL;

239. LOG (M,n)
Gets the number of m as the base N.
SELECT LOG (2,8) from DUAL;

A. MOD (m,n)
Gets the remainder of m divided by N.
SELECT MOD (100,7) from DUAL;

241. Power (M,n)
Get M's n 冪.
SELECT Power (4,3) from DUAL;

242. ROUND (n,m)
Rounds N to the decimal point after M bit.
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 from DUAL;

244. SINH (N)
Gets the hyperbolic sinusoidal value of N.
SELECT SINH (1) from DUAL;

245. SORT (N)
Get the square root of N, n>=0
SELECT SORT (9) from DUAL;

246. TAN (N)
Gets the tangent value of N.
SELECT TAN (0) from DUAL;

247. TANH (N)
Gets the hyperbolic tangent value of N.
SELECT TANH (0) from DUAL;

248. TRUNC (n,m)
Gets the value of n that is truncated at M bit.
SELECT TRUNC (7.7788,2) from DUAL;

249. COUNT ()
Calculate the number of full conditions.
SELECT COUNT (*) from TABLE1 WHERE col1= ' AAA ';

MAX ()
asks for the maximum value for the specified column.
SELECT MAX (COL1) from TABLE1;

251. MIN ()
asks for the minimum value for the specified column.
SELECT MIN (COL1) from TABLE1;

252 AVG ()
Averages the specified columns.
SELECT AVG (COL1) from TABLE1;

253. SUM ()
Calculate the and of the columns.
SELECT SUM (COL1) from DUAL;

254. To_number (CHAR)
Convert the characters to numbers.
SELECT to_number (' 999 ') from DUAL;

To continue ...


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.