Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Common functions of ORACLE

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.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report