Oracle common function highlights

zhaozj2021-02-12  180

Value function:

Absolute value of ABS (M) M

MOD (M, N) M is after the remainder

N times of Power (M, N) M

Round (M [, N]) M is rounded to the value of N bit after the decimal point (n default is 0)

Trunc (M [, N]) m truncates the value of N-bit digits (n default is 0)

Character function:

Initcap (ST) returns St Writing the first letters of each word, all other letters lowercase

Lower (ST) Returns ST to write all the letters of each word

Upper (ST) Returns ST to write all the letters of each word

Concat (ST1, ST2) Returns the end of ST2 to ST1 (available operator || ")

LPAD (ST1, N [, ST2]) returns to the rightmost ST, ST is filled with ST2 in the left side of ST1 until the default space of the length n, ST2 is spaced.

RPAD (ST1, N [, ST2]) returns the left aligned ST, ST is filled with ST2 in the right side of ST1 until the default space of the length n, ST2 is spaced.

LTRIM (ST [SET]) Returns St. St. The character is deleted from the left until the first one is not a set. By default, it is a space.

RTRIM (ST [SET]) Returns St. ST is a character in the SET from the right until the first one is not a set. By default, it is a space.

Replace (ST, Search_st [, Replace_St]) will replace each time SEARCH_ST replace with Replace_ST, returns a ST. Delete Search_ST when default

SubStr (ST, M [, N]) n = Returns the substring of the ST string, starting from the M position, taking N characters long. Over the default, return to the end of the ST

LENGTH (ST) value, return the number of characters in ST

Instr (ST1, ST2 [, M [, N]]) value, returns ST1 starting from the Mth character, the position of the nth occurrence of ST2, the default value of M and N is 1

example:

1.

SELECT INITCAP ('Thomas'), INITCAP ('Thomas') from test;

INITCA INITCA

------ ------

Thomas thomas

2.

Select Concat ('ABC', 'DEF') "First" from test;

First

-----

ABCDEF

3.

SELECT 'ABC' || '' || 'DEF' "First" from test;

First

-----

ABC DEF

4.

Select LPAD (Name, 10), RPAD (Name, 5, '*') from test;

LPAD (Name, 10) RPAD (Name, 5, '*')

------------ ----------------

MMX MMX **

Abcdef abcde

5.

Remove points and words ST and RD at the end of the address field

SELECT RTRIM (Address, '. st rd') from test

6.

Select Name, Replace (Name, 'A', '*') from test;

Name Replace (Name, 'A', '*')

---- ---------------------

Great greet

7.

Select Substr ('Archibald Bearisol', 6, 9) A, Substr ('Archibald Bearisol', 11) b from test;

a b

------- ------- Bald Bear Bearisol

8.

Select Name, INSTR (Name, '') A, INSTR (Name, ', 1, 2) b from test;

Name a b

-----------------------

Li Lei 3 0

L i l 2 4

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Conversion Function: NVL (M, N) If the M value is NULL, return n, otherwise return MTO_CHAR (M [, FMT]) m to convert the width of the FMT value when converted from one value to the specified format, the width of the FMT value Receiving all valid digital to_number (ST [, FMT]) ST is converted from the characteristic data into values ​​in the specified format, the size of the value of the value is just the format of the TO_CHAR () function: --- ------------------------------Symbol Description------------------ -------------- 9 Each 9 represents a number 0 represents the pilot 0 $ dollar symbol to display Print any local currency symbol on the left of the number. Print decimal The decimal point, print represents a comma ------------------------------- Example 1.Select TO_NUMBER ( '123.45') TO_NUMBER ('234.56') Form test; to_number ('123.45') to_number ('234.56') ----------------------- ----------------- 358.012.select to_char (987654321) from test; to_char (987654321) ------------------ 9876543213 .select to_char (123, '$ 9,999,999) A, TO_CHAR (54321,' $ 9,999,999) B, TO_CHAR (9874321, '$ 9,999,999) c from test; a b c ---------------------- ---- - - --------- $ 123 $ 54,321 $ 9,874,3214.Select to_CHAR (1234.1234, '999, 999.999) A, To_Char (0.4567,' 999, 999.999) B, TO_CHAR (1.1, '999, 999.999) From test; a b c ---------------------------- 1 , 234.123 .457 1.100

Group function:

AVG ([DistINCT / All] n) column N average

COUNT ([all] *) Returns the number of rows within the query includes repetition values ​​and null values

COUNT ([Distinct / All] n) Non Null Value

Max ([DistINCT / All] n) This column or expression is maximum

min ([Distinct / All] n) This column or expression minimum

Stdev ([Distinct / All] n) This column or expression standard deviation, ignore null value

SUM ([Distinct / All] n) This column or expression of the sum

Variance ([DISTINCT / ALL] N) This column or expression variance, ignore null value

Date function:

>

Add_MONTHS (D, N) Date D plus N month Last_Day (D) contains the date of the last day of the month

Month_between (D, E) Date D and E Moon number, E first in D

New_time (d, a, b) a time zone date and time D Date and time

Next_day (d, day) is better than the date D, the date specified by day

Sysdate Current System Date and Time

The last date in the date list given by GreateSt (D1, D2, ... DN)

The earliest date in the date given by LEAST (D1, K2, ... DN)

TO_CHAR (D [, FMT]) Date D Transitions to strings in the format specified by FMT

TO_DATE (ST [FMT]) String ST is transferred to the date value specified by the FMT, if FMT is ignored, St To use the default format

Round (D [D [FMT]) Date D Press FMT to specify the format to the nearest date

Trunc (D [, FMT]) Date D Press FMT to specify the format to cut to the nearest date

Attachment:

Date format:

--------------------------------

Format code Description Example or can be valued

--------------------------------

DD this month 1-3

DY three uppercase letters said with a few Sun, ... SAT

Day complete week, uppercase English Sunday, ... Saturday

MM month 1-12

Mono three uppercase letters indicated by Jan, ... DEC

Month complete january, ... decEmber

RM Number I, ... XII

Two digits, four digits of YY or YYYY

HH: MI: SS Time: Score: Second

HH12 or HH24 is displayed in 12 hours or 24 hours

MI points

SS seconds

AM or PM on the afternoon indicator

SP suffix SP requires spelling any numerical field

The TH suffix th indicates that the added number is the number of orders 4th, 1st

FM prefix to the month or day or year, prohibited fill

---------------------------------example:

1.

Next Friday date

SELECT NEXT_DAY (Sysdate, 6) from test;

2.

Today's date two months ago

SELECT add_months (sysdate, -2) from test;

http://blog.9cbs.neet/hbzxf

转载请注明原文地址:https://www.9cbs.com/read-6976.html

New Post(0)