《ORACLE常用数值函数、转换函数、字符串函数介绍.docx.pdf》由会员分享，可在线阅读，更多相关《ORACLE常用数值函数、转换函数、字符串函数介绍.docx.pdf（7页珍藏版）》请在三一文库上搜索。
1、ORACLE USES numeric functions, transformation functions, and string functions? Numerical function: The absolute value of abs (m) m Mod m (n) m is the remainder of n Power (m, n) m to the n Round (m n) m four round the decimal point (n default is 0). Trunc (m n) m intercepts the value of n bits (n de
2、faults to 0) Character function: Initcap (st) returns st to capitalize the first letter of each word, all other letter lowercase Lower (st) returns st to all lowercase letters of each word Upper (st) returns st to capitalize the letters of each word Concat (stl, st2) returns st to st2 to the end of
3、stl (the available operator | |) The lpad (stl, n , st2) returns the right-aligned st, which is filled to the left of stl with st2 filling up until the length is n, and the default for st2 is space The rpad (stl, n , st2) returns the left aligned st, which is filled to the right of stl with st2 fill
4、ing up until the length is n, and the default of st2 is whitespace Ltrim (st , set) returns st, which removes the character from the set from the left until the first is not a character in the set. By default, it means space Rtrim (st , set) returns st, which removes the character from the set from
5、the right until the first is not a character in the set? By default, it means space Replace (st, search_st , replace_st) replaces the search_st with replace_st in st and returns a st. By default, delete search_st substr (st, m , n) n = the substring of the st string, starting at m and taking n chara
6、cters long. By default, you always return to the st end The length (st) value, which returns the number of characters in st Instr (stl, st2 , m , n) numeric, returns stl from the m character, st2 the NTH occurrence, the default value of m and nisi: 1 select initcap ( THOMAS ) from test; The initca i
7、nitca - 一一 一一 一一Thomas Thomas Select concat ( ABC def) first from test; First - -abcdef Select ABC | | | | def first “from test? First ABC def The select lpad (name, 10), the rpad (name, 5, *) from test; The lpad (name, 10) rpad (name, 5, *) -MMX MMX * abcdef abode Remove the dot and the word st and
8、 rd select rtrim (address, ? st rd ) from test Select name, replace (name, a, ) from test; Name, ，a, - great gre * t Select substr ( Archibald bearisol , 6, 9) a, substr ( Archibald bearisol 11) Bald bear bearisol Select name, instr (name, ) a, instr (name, , , 1, 2) b from test; Name a to b - - l i
9、 lei 3 0 I 1 1 2 4% Conversion function: NVL (m, n) if the m value is null, return n, otherwise the m is returned To_char (m , FMT) m from a numeric value converted to the specified format string FMT by default, the width of the FMT value just can accommodate all of the valid number to_number (st ,
10、FMT) st from character data into a numerical value in the specified format, Default coincided with the size of the numeric format string for the whole number of attached: to_char () function formats: symbolic description - - 9 each 9 represents the result of a number is 0 to display the forerunner 0
11、 $symbol printed on the left side of the number of L any local currency symbo 1. Print the decimal point, printed on behalf of micrometer a comma - cases: Select to_number ( 123? 45 ) + to_number ( 234. 56 ) form test: To_number ( 123? 45 ) + to_number ( 234. 56 ) - 2. 358? 01 the select to_char (98
12、7654321) from the test. To_char (987654321) 987654321-3. Select to_char (123, $9999999). A, to_char (54321, $9999999) b, to_char (9874321, $9999999) c from the test. Abe - $123 $54321 $54321 4. Select to_char (1234. 1234, 999999999 , ). A, to_char (0. 4567, 999999999)b, to_char (1. 1, 999999999 , )
13、from the test. Abe 1234123. 457 1. 100 Group functions: The average of the column n Count (all *) returns the number of rows within the query range including the duplicate and null values Count (distinct/all n) the number of rows of non-null values Max (distinct/all n) the maximum value of the colum
14、n or expression Min (distinct/all n) the minimum value of the column or expression Stdev (distinct/all n) the standard deviation of the column or expression, ignoring the null value Sum (distinct/all n) the sum of the column or expression Variance (distinct/all n) the variance of the column or expre
15、ssion, ignore null - Date function: Addjnonths (d, n) date d plus n months Last day (d) contains the date of the last day of the month of d Month_between (d, e) date d and e, e before d New_time (d, a, b) a time zone and date and time in b time zone Next_day (d, day) is later than date d, the date s
16、pecified by day Sysdate current system date and time The last date in the list of dates given by the greatest (dl, d2, . . . dn) The earliest date in the list of dates given by the least (dl, k2, . . . dn) The to_char (d FMT) date is converted to a string in the format specified by FMT The to_date (
17、st FMT) string is converted to a date value in the format specified by FMT, and if the FMT is ignored, the st should be in the default format The round (d FMT) date d is specified in FMT to the nearest date The trunc (d FMT) date is truncated to the latest date by FMT: Date format: - Format code ins
18、tructions For example, or the range of values DD is 1-3 times a day The three capital letters of the week, SUN,? The SAT DAY is a full week. SATURDAY MM January 1-12 MON 3 A capital letter of the month JAN,. DEC full JANUARY,? DECEMBER RM the Roman numeral I,. Assist YY or YYYY two, four digits in H
19、H: MI: SS: poin ts: seco nds HH12 or HH24 in 12 hours or 24 hours according to MI SS seconds indicator on AM or PM afternoon SP suffix SP requirements spell out any numeric fields TH suffix TH said add Numbers is ordinal nu mb er 4 TH, 1 st FM prefix for month or day or year values, ban fill 一一 一一 一一 一一 一一 一一 一一 一一 一一- cases: Next Frida.y ， s date, select next_day (6) from test? Two months ago todays date, select add_months (-2) from test;