In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-22 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Classification of oracle functions
1.REPLACE ('string','s1','s2')
The character or variable that string wants to be replaced
S1 replaced string
S2 string to replace
SQL > select replace ('he love you','he','i') from dual
REPLACE ('H
-
I love you
Case: replace KING with King in the name of ename in the emp table
Sql > select replace (ename,'KING','King') from emp
As a result, you see KING replaced by King
Sql > select ename from emp
As a result, I see KING or KING.
Summary replace replacements only show the results of substitutions in the query.
2.INSTR (C1 ~ C2 ~ I ~ J)
Searches a string for the specified character and returns the location where the specified character was found
C1 string to be searched
The string C2 wants to search for
I start position of the search. Default is 1.
Location where J appears. Default is 1.
SQL > select instr ('oracle traning','ra',1,2) instring from dual
INSTRING
-
nine
3.ASCII
Returns the decimal number corresponding to the specified character
SQL > select ascii ('A') A space from dual ascii ('a') a zero,ascii ('0')
An A ZERO SPACE
-
65 97 48 32
4.CHR
Give the integer and return the corresponding character
SQL > select chr (54740) zhao,chr (65) chr65 from dual
ZH C
--
Zhao A
5.CONCAT
Concatenate two strings
SQL > select concat ('010 Summerfield Magazine' 88888888') | | 'transfer to 23' phone from dual
Telephone
-
010-88888888 transfer 23
6.INITCAP
Returns the string and capitalizes the first letter of the string
SQL > select initcap ('smith') upp from dual
UPP
--
Smith
7.SUBSTR (string,start,count)
Fetch substrings, starting with start, and fetch count
SQL > select substr ('1308888888888) from dual
SUBSTR ('
-
08888888
8.LOWER
Returns the string and lowercase all characters
SQL > select lower ('AaBbCcDd') AaBbCcDd from dual
AABBCCDD
-
Aabbccdd
9.UPPER
Returns the string and capitalizes all characters
SQL > select upper ('AaBbCcDd') upper from dual
UPPER
-
AABBCCDD
10.RPAD and LPAD (paste characters)
RPAD pastes characters on the right side of the column
LPAD pastes characters to the left of the column
SQL > select lpad (rpad ('gao',10,'*'), 17pr gao',10,'*'') from dual
LPAD (RPAD ('GAO',1)
-
* gao*
If there are not enough characters, fill it with *.
Case study:
A total of 15 people are long and the left side is not enough to fill it up.
Select lpad ('yuemu.blog',15,'*') from dual
There are a total of 17 long, but there is not enough on the right * fill.
Select rpad (lpad ('yuemu.blog',15,'*'), 17thjime') from dual
11.LTRIM and RTRIM
LTRIM deletes the string that appears on the left
RTRIM deletes the string that appears on the right
SQL > select ltrim (rtrim ('gao qian jing','') from dual
LTRIM (RTRIM ('
-
Gao qian jing
Case: remove the * on the left and right side.
Select rtrim (ltrim ('* * yuemu.blog***','*'),'*') from dual
12.TRIM (leading's' from 'string')
LEADING cut out the preceding characters
TRAILING, cut out the following characters.
If not specified, the default is a space character
Case: delete y
Select trim ('y' from 'yuemu.blog') from dual
13.CEIL (rounding up)
Returns the smallest integer greater than or equal to the given number
SQL > select ceil (3.1415927) from dual
CEIL (3.1415927)
-
four
14.EXP
Returns the n-th root of a number e
SQL > select exp (2), exp (1) from dual
EXP (2) EXP (1)
-
7.3890561 2.7182818
15.FLOOR
Take an integer to a given number
SQL > select floor (2345.67) from dual
FLOOR (2345.67)
-
2345
16.MOD (N1 and N2)
Returns the remainder of N1 divided by N2
SQL > select mod (10jue 3), mod (3pr 3), mod (2pr 3) from dual
MOD (10) MOD (3) MOD (2)
1 0 2
17.POWER
Returns the N2 root of N1
SQL > select power (2jue 10), power (3pr 3) from dual
POWER (2Jing 10) POWER (3BI 3)
1024 27
18.ROUND and TRUNC
Round off according to the specified precision
Round (rounded), trunc (intercept, you can specify which bit to intercept, and intercept to an integer by default)
SQL > select round (55.5), round (- 55.4), trunc (55.5), trunc (- 55.5) from dual
ROUND (55.5) ROUND (- 55.4) TRUNC (55.5) TRUNC (- 55.5)
56-55 55-55
Select trunc (127.177 and 2) from dual-display 127.17
Select trunc (127.177) from dual-display 100
19.SIGN
Take the symbol of the number n, greater than 0 returns 1, less than 0 returns-1, equals 0 returns 0
SQL > select sign, sign (- 100), sign (0) from dual
SIGN (123) SIGN (- 100) SIGN (0)
1-1 0
20.ADD_MONTHS
Increase or subtract months
SQL > select to_char (add_months (to_date ('199912'), 2), 'yyyymm') from dual
TO_CHA
--
200002
SQL > select to_char (add_months (to_date ('199912'),-2), 'yyyymm') from dual
TO_CHA
--
199910
Case study: if you want to view employees who have joined the workforce in recent months
Inquire about those who have joined the job within 34 years.
Select * from emp where add_months (hiredate,408) > = sysdate
21.LAST_DAY
Returns the last day of the date
SQL > select to_char (last_day (sysdate), 'yyyy-mm-dd') from dual
LAST_DAY (S
-
2015-10-21
22.MONTHS_BETWEEN (date2,date1)
Give the month of the date2-date1
SQL > select months_between ('19-12-1999 mon_between from dual
MON_BETWEEN
-
nine
SQL > select months_between (to_date ('2000.05.20mm. Dd'), to_date (' 2005.05.20mm. DD') mon_betw from dual
MON_BETW
-
-60
23.NEW_TIME (date,'this','that')
Give the date and time in the this time zone = other time zone
SQL > select to_char (sysdate,'yyyy.mm.dd hh34:mi:ss') bj_time,to_char (new_time
2 (sysdate,'PDT','GMT'), 'yyyy.mm.dd hh34:mi:ss') los_angles from dual
BJ_TIME LOS_ANGLES
--
2004.05.09 11:05:32 2004.05.09 18:05:32
24.NEXT_DAY (date,'day')
Calculate the date of the next week after giving the date date and week x
SQL > select next_day ('18-May-2001 'Friday') next_day from dual
NEXT_DAY
-
25-May-01
25.CONVERT (crecoery dsetmensset)
Convert the source string sset from one language character set to another destination dset character set
SQL > select convert ('strutz','we8hp','f7dec') "conversion" from dual
Conver
--
Strutz
26.TO_CHAR (atde,'format')
Convert one format to another, display selectively, and display the content according to your expectations
SQL > select to_char (sysdate,'yyyy/mm/dd hh34:mi:ss') from dual
TO_CHAR (SYSDATE,'YY
-
2004-05-09 21:14:41
Case 1:
Select ename,to_char (sal,'L99G999D99') from emp case 2:
Select ename,to_char (hiredate,'yyyy-mm-dd hh34:mi:ss') from emp
Case 3: inquire about employees who have joined the workforce since 1982
Select ename from emp where to_char (hiredate,'yyyy') > = 1982
27.TO_DATE (string,'format')
Convert a string to a date in ORACLE
28.TO_NUMBER
Convert a given character to a number
SQL > select to_number ('1999') year from dual
YEAR
-
1999
29.decode
Display different information according to a column number in the table
Select ename,decode (Department deptno,10,'10'
20) Department 20) from emp
30.GREATEST
Returns the maximum value in a set of expressions, that is, the encoding size of comparison characters.
SQL > select greatest ('AA','AB','AC') from dual
GR
-
AC
SQL > select greatest ('ah', 'Ann', 'day') from dual
GR
-
day
31.LEAST
Returns the minimum value in a set of expressions
SQL > select least ('ah', 'Ann', 'day') from dual
LE
-
Ah!
32.UID
Returns a unique integer that identifies the current user
SQL > show user
USER is "GAO"
SQL > select username,user_id from dba_users where user_id=uid
USERNAME USER_ID
GAO 25
33.AVG (DISTINCT | ALL)
All means to average all values, while distinct averages only different values.
SQLWKS > create table table3 (xm varchar (8), sal number (7))
Statement has been processed.
SQLWKS > insert into table3 values ('gao',1111.11)
SQLWKS > insert into table3 values ('gao',1111.11)
SQLWKS > insert into table3 values ('zhu',5555.55)
SQLWKS > commit
SQL > select avg (distinct sal) from gao.table3
AVG (DISTINCTSAL)
-
3333.33
SQL > select avg (all sal) from gao.table3
AVG (ALLSAL)
-
2592.59
34.MAX (DISTINCT | ALL)
To find the maximum value, ALL means to find the maximum value for all values, DISTINCT means to find the maximum value for different values, and only take the same value once.
SQL > select max (distinct sal) from scott.emp
MAX (DISTINCTSAL)
-
5000
35.MIN (DISTINCT | ALL)
To find the minimum, ALL means to minimize all values, DISTINCT means to minimize different values, and only take the same value once.
SQL > select min (all sal) from gao.table3
MIN (ALLSAL)
-
1111.11
Here I will only introduce some common functions, and there are many functions that you can find on your own.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.