Common Oracle questions 1000 (6)

204. How do I obtain the ASCII value of the first character of a string?
ASCII (char)
Select ASCII ('abcde') from dual;
Result: 65

205. How do I get the characters specified by data value n?
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 can I replace the numeric value in a column with 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 character string char is a large character, and the rest is a small character.
Select initcap ('abcde') from dual;

209. Length (char)
Returns the length of a string char.
Select length ('abcde') from dual;

210. Lower (char)
Converts all char strings to small characters.
Select lower ('abcde') from dual;

211. lpad (char1, N, char2)
Enter char1 from the left of the character string char2 to make it grow to n.
Select lpad ('abcdefg', 10' 123 ') from dual;
Result: '123abcdefg'

212. ltrim (char, set)
Removes characters from the character set from the left margin of the character string char until the first character is not in the set.
Select ('cdefg', 'cd') from dual;
Result: 'efg'

213. nls_initcap (char)
Take the first character of char as a large character, and the remaining characters as small characters.
Select nls_initcap ('abcde') from dual;

214. nls_lower (char)
All characters including the character string Char are small bytes.
Select nls_lower ('aaa') from dual;

215. nls_upper (char)
Contains all the characters in the string char.
Select nls_upper ('aaa') from dual;

216. Replace (char1, char2, char3)
Use the character string char3 to replace the char2 column. The result is placed in char1.
Select Replace (emp_no, '20140901', '20160901') from dual;

217. rpad (char1, N, char2)
Use character string char2 to enter character string char1 right, so that the length is N.
Select rpad ('20140901', 8, '0') from dual;

218. rtrim (char, set)
Remove the character in the character set of the string char right character until the last character is not in the set.
Select rtrim ('abcde', 'de') from dual;

219. substr (char, m, n)
Obtain the n characters starting from m in the string Char. The character is a character. One character is a character.
Select substr ('abcde', 2, 3) from dual;

220. substrb (char, m, n)
Obtain the n characters starting from m in the string Char. The character string contains two characters.
Select substrb ('abcde', 2, 3) from dual;

221. Translate (char1, char2, char3)
Replace char2 in char1 with char3.
Select translate ('abcdefgh', 'de', 'mn ') from dual;

222. Upper (char)
Converts all character strings into large bytes.

223. add_months (d, n)
Add n months to the D-day period.
Select add_months (sysdate, 5) from dual;

224. last_day (d)
Obtain the date of the last day of the month that contains the dday period.
Select last_day (sysdate) from dual;

225. month_between (D1, D2)
Obtain the number of months between the two dates.
Select month_between (D1, D2) from dual;

226. next_day (D, char)
Get the date of the first Sunday named by char later than date D.
Select next_day (to_date ('2014/1/20'), 'satday') from dual;

227. rount (D, FMT)
Obtains the progressive date rounded to by the specified FMT mode.
Select rount ('2014/1/20', month) from dual;

228. sysdate
Obtain the date and time of the current system.
Select sysdate from dual;

229. to_char (D, FMT)
Returns the string that matches the date d into FMT.
Select to_char (sysdate, 'yyyy/MM/dd') from dual;

230. to_date (char, FMT)
Returns the string char as a date in FMT Format.
Select to_date ('2014/1/20', 'yyyy/MM/dd') from dual;

231. Abs (N)
Returns the round value of N.
Select ABS (-6) from dual;

232. Ceil (N)
Obtain the maximum integer greater than or equal to n.
Select Ceil (1, 5.6) from dual;

233. Cos (N)
Returns the cosine of N.
Select cos (1) from dual;

234. Sin (N)
Returns the sine of N.
Select sin (1) from dual;

235. cosh (N)
Returns the cosine of N.
Select cosh (1) from dual;

236. exp (N)
Obtain n times of E.
Select exp (1) from dual;

237. Floor (N)
Returns the smallest integer less than or equal to n.
Select floor (5.6) from dual;

238. ln (N)
Obtain the natural logarithm of N.
Select ln (1) from dual;

239. Log (m, n)
Obtain the logarithm of n Based on M.
Select log (2, 8) from dual;

240. Mod (m, n)
Returns the remainder of m divided by N.
Select Mod (100,7) from dual;

241. Power (m, n)
Obtain N records of M.
Select power (4, 3) from dual;

242. Round (n, m)
Round n to the M-bit behind a small number.
Select (78.87653, 2) from dual;

243. Sign (N)
When N is less than 0,-1 is obtained;
When N> 0, 1 is returned;
When n = 0, 0 is obtained;
Select sign (99) from dual;

244. sinh (N)
Obtain the sine of N.
Select sinh (1) from dual;

245. Sort (N)
Obtain the square root of N, N> = 0.
Select sort (9) from dual;

246. Tan (N)
Returns the tangent of N.
Select Tan (0) from dual;

247. Tanh (N)
Obtain the positive tangent value of N.
Select Tanh (0) from dual;

248. trunc (n, m)
Obtain the n Value of the M-bit truncated substring.
Select trunc (7.7788, 2) from dual;

249. Count ()
Calculate the number of logs that meet the requirements.
Select count (*) from Table1 where col1 = 'aaa ';

250. Max ()
Returns the maximum value for the specified column.
Select max (col1) from Table1;

251. Min ()
Calculates the minimum value for the specified column.
Select min (col1) from Table1;

252. AVG ()
Calculates the average value for the specified column.
Select AVG (col1) from Table1;

253. sum ()
Calculate the sum of columns.
Select sum (col1) from dual;

254. to_number (char)
Returns the numeric value of a character.
Select to_number ('123') from dual;