Oracle functions

Time:2020-11-18

1. ASCII returns the decimal number corresponding to the specified character;

SQL> select ascii(‘A’) A,ascii(‘a’) a,ascii(‘0’) zero,ascii(‘ ‘) space from dual;

A         A      ZERO     SPACE
——— ——— ——— ———
65        97        48        32

2. Chr gives an integer and returns the corresponding character;

SQL> select chr(54740) zhao,chr(65) chr65 from dual;

ZH C
— –
Zhao a

3. Concat connects two strings;

SQL > select concat (‘010 – ‘,’88888888’), transfer to 23’gao Qianjing phone from dual;

Gao Qianjing telephone
—————-
010-88888888 to 23

4. Initcap returns a string and changes the first letter of the string to uppercase;

SQL> select initcap(‘smith’) upp from dual;

UPP
—–
Smith

5. Instr (C1, C2, I, J) searches for the specified character in a string and returns the position where the specified character is found;

C1 string searched
C2 the string you want to search for
I is the starting position of the search. The default value is 1
Where j appears, the default value is 1
SQL> select instr(‘oracle traning’,’ra’,1,2) instring from dual;

INSTRING
———
9

6. Length returns the length of the string;

SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from .nchar_tst;

NAME   LENGTH(NAME) ADDR             LENGTH(ADDR)       SAL LENGTH(TO_CHAR(SAL))
—— ———— —————- ———— ——— ——————–
Gao Qianjing 3 Haiding District, Beijing 6 9999.99 7

7. Lower returns a string and lowercase all characters

SQL> select lower(‘AaBbCcDd’)AaBbCcDd from dual;

AABBCCDD
——–
aabbccdd

8. Upper returns a string and capitalizes all characters

SQL> select upper(‘AaBbCcDd’) upper from dual;

UPPER
——–
AABBCCDD

9. Rpad and lpad (paste character)

Rpad pastes characters to the right of the column
Lpad pastes characters to the left of the column
SQL> select lpad(rpad(‘gao’,10,’*’),17,’*’)from dual;

LPAD(RPAD(‘GAO’,1
—————–
*******gao*******
If there are not enough characters, fill them with *

10. Ltrim and rtrim

Ltrim removes 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

11.SUBSTR(string,start,count)

Take substrings, starting from start, take count
SQL> select substr(‘13088888888’,3,8) from dual;

SUBSTR(‘
——–
08888888

12.REPLACE(‘string’,’s1′,’s2′)

String: the character or variable to be replaced
S1 replaced string
S2 string to replace

SQL> select replace(‘he love you’,’he’,’i’) from dual;

REPLACE(‘HELOVEYOU’,’HE’,’I’)
——————————
i love you

13. Soundex returns a string with the same pronunciation as the given string

SQL> create table table1(xm varchar(8));
SQL> insert into table1 values(‘weather’);
SQL> insert into table1 values(‘wether’);
SQL> insert into table1 values(‘gao’);

SQL> select xm from table1 where soundex(xm)=soundex(‘weather’);

XM
——–
weather
wether

14.TRIM(‘s’ from ‘string’)

Cutting the preceding characters
Training cuts the following characters
If not specified, the default is the space character

15. ABS returns the absolute value of the specified value

SQL> select abs(100),abs(-100) from dual;

ABS(100) ABS(-100)
——— ———
100       100

16. ACOS gives the value of arccosine

SQL> select acos(-1) from dual;

ACOS(-1)
———
3.1415927

17. Asin gives the value of arcsine

SQL> select asin(0.5) from dual;

ASIN(0.5)
———
.52359878

18. Atan returns the arctangent of a number

SQL> select atan(1) from dual;

ATAN(1)
———
.78539816

19. Ceil returns the smallest integer greater than or equal to the given number

SQL> select ceil(3.1415927) from dual;

CEIL(3.1415927)
—————
4

20. Cos returns the cosine of a given number

SQL> select cos(-3.1415927) from dual;

COS(-3.1415927)
—————
-1

21. Cosh returns a numeric arccosine

SQL> select cosh(20) from dual;

COSH(20)
———
242582598

22. Exp returns the nth root of a number E

SQL> select exp(2),exp(1) from dual;

EXP(2)    EXP(1)
——— ———
7.3890561 2.7182818

23. Floor takes an integer for a given number

SQL> select floor(2345.67) from dual;

FLOOR(2345.67)
————–
2345

24. Ln returns the logarithm of a number

SQL> select ln(1),ln(2),ln(2.7182818) from dual;

LN(1)     LN(2) LN(2.7182818)
——— ——— ————-
0 .69314718     .99999999

25. Log (N1, N2) returns the logarithm of N2 based on N1

SQL> select log(2,1),log(2,4) from dual;

LOG(2,1)  LOG(2,4)
——— ———
0         2

26. Mod (N1, N2) returns the remainder of N1 divided by N2

SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;

 

MOD(10,3)  MOD(3,3)  MOD(2,3)

——— ——— ———

1         0         2

27. Power returns the N2 root of N1

SQL> select power(2,10),power(3,3) from dual;

POWER(2,10) POWER(3,3)

———– ———-

1024         27

28. Round and TRUNC

Rounds to the specified precision

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

29. Sign takes the sign of the number n, greater than 0 returns 1, less than 0 returns – 1, equals 0 returns 0

SQL> select sign(123),sign(-100),sign(0) from dual;

SIGN(123) SIGN(-100)   SIGN(0)

——— ———- ———

1         -1         0

30. Sin returns the sine value of a number

SQL> select sin(1.57079) from dual;

SIN(1.57079)

————

1

31. Sight returns the value of hyperbolic sine

SQL> select sin(20),sinh(20) from dual;

 

SIN(20)  SINH(20)

——— ———

.91294525 242582598

32. Sqrt returns the root of the number n

SQL> select sqrt(64),sqrt(10) from dual;

SQRT(64)  SQRT(10)

——— ———

8 3.1622777

33. Tan returns the tangent of a number

SQL> select tan(20),tan(10) from dual;

TAN(20)   TAN(10)

——— ———

2.2371609 .64836083

34.TANH

Returns the hyperbolic tangent of the number n

SQL> select tanh(20),tan(20) from dual;

TANH(20)   TAN(20)

——— ———

1 2.2371609

35.TRUNC

Intercepts a number with the specified precision

SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;

TRUNC1 TRUNC(124.16666,2)

——— ——————

100             124.16

36.ADD_MONTHS

Add or subtract months

SQL> select to_char(add_months(to_date(‘199912′,’yyyymm’),2),’yyyymm’) from dual;

TO_CHA

——

200002

SQL> select to_char(add_months(to_date(‘199912′,’yyyymm’),-2),’yyyymm’) from dual;

TO_CHA

——

199910

37.LAST_DAY

The last day of the return date

SQL> select to_char(sysdate,’yyyy.mm.dd’),to_char((sysdate)+1,’yyyy.mm.dd’) from dual;

TO_CHAR(SY TO_CHAR((S

———- ———-

2004.05.09 2004.05.10

SQL> select last_day(sysdate) from dual;

LAST_DAY(S

———-

31-may-04

38.MONTHS_BETWEEN(date2,date1)

Give the month of date2-date1

SQL> select months_ Between (’19-dec-1999 ‘,’19-mar-1999’) mon_ between from dual;

MON_BETWEEN

———–

9

SQL>selectmonths_between(to_date(‘2000.05.20′,’yyyy.mm.dd’),to_date(‘2005.05.20′,’yyyy.dd’)) mon_betw from dual;

MON_BETW

———

-60

39.NEW_TIME(date,’this’,’that’)

Give the date and time in this time zone = other time zone

SQL> select to_char(sysdate,’yyyy.mm.dd hh24:mi:ss’) bj_time,to_char(new_time

2  (sysdate,’PDT’,’GMT’),’yyyy.mm.dd hh24:mi:ss’) los_angles from dual;

BJ_TIME             LOS_ANGLES

——————- ——————-

2004.05.09 11:05:32 2004.05.09 18:05:32

40.NEXT_DAY(date,’day’)

Calculate the date of the week after date X

SQL> select next_ Day (’18-may-2001 ‘,’friday’) next_ day from dual;

NEXT_DAY

———-

25-may-01

41. Sysdate is used to get the current date of the system

SQL> select to_char(sysdate,’dd-mm-yyyy day’) from dual;

TO_CHAR(SYSDATE,’

—————–

09-05-2004 Sunday

TRUNC (date, FMT) truncates the date according to the given requirements. If FMT = Mi ‘represents reserved minutes, truncate seconds

SQL> select to_char(trunc(sysdate,’hh’),’yyyy.mm.dd hh24:mi:ss’) hh,

2  to_char(trunc(sysdate,’mi’),’yyyy.mm.dd hh24:mi:ss’) hhmm from dual;

HH                  HHMM

——————- ——————-

2004.05.09 11:00:00 2004.05.09 11:17:00

42. Chartorowid converts a character data type to a ROWID type

SQL> select rowid,rowidtochar(rowid),ename from scott.emp;

ROWID              ROWIDTOCHAR(ROWID) ENAME

—————— —————— ———-

AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH

AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN

AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD

AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES