Common oracle functions (1): oracle Functions

Common oracle functions (1): oracle Functions

1. Return the decimal number corresponding to the specified character

Select ascii ('A') A, ascii ('Z') A, ascii ('12') a dozen, ascii ('') kg from dual;

2. returns the character corresponding to the specified decimal number.

select chr(65) A,chr(122) z from dual;

3. connect two strings

Select concat ('xiong da', 'xiong 2') constr from dual; -- Xiong daxiong 2

4. Change the first character to uppercase and return the string

select initcap('boat') upperfirst from dual;--Boat

5. Convert all characters into uppercase letters and return strings.

select upper('boat') upperall from dual t;--BOAT

6. Convert all characters into lowercase letters and return strings.

select lower('BoaT') lowerall from dual;--boat

7. INSTR (str1, str2, a, B) Functions

Usage: Obtain the position where str2 is contained in str1.

Check from the left. the start position is a. If a is a negative number, the scan starts from the right, and the position where B appears will be returned.

Both a and B are set to 1 by default, which will return the position where str2 appears for the first time in str1.

select instr('zheshigeceshi','sh',-2,1) str from dual;--11select instr('zheshigeceshi','sh',1,2) str from dual;--11

8. Get the string length

select length('boat') len from dual;--4

9. lpad (str, n, [pad_string]) Function

The str parameter can be a character or a parameter.

Parameter n: the length of the returned string. If the number is shorter than the length of the original string, the lpad function truncates the string into n characters from left to right;

The parameter pad_string is an optional parameter. This string is the string to be pasted to the left of the string. If this parameter is not written, the lpad function will paste a space on the left of the string.

select rpad('boat',10,'*') from dual t;--boat******select lpad('boat',10,'*') from dual t;--******boat

10. ltrim (x, y) Functions

Usage: truncates the characters in x one by one based on the characters in y, and starts from the left.

As long as some characters in y are met, all characters in x will be truncated until the Function Command ends until the characters in y are met. rtrim (y, x) is the same

select ltrim('boat','bo') from dual;--atselect ltrim('booooobbbbobat','bo') from dual t;--atselect rtrim('boat','at') from dual;--boselect rtrim('boaaaaaaaaatttttttaat','at') from dual;--bo

11. substr (string str, int a, int B) Function

Parameter 1: string to be processed by str

Parameter 2: a truncates the start position of the string (the start position is 0). If it is a negative value, it indicates that the string starts from the end.

Parameter 3: the length of the string intercepted by B. If a B-ultrasound returns the length of the string to be processed, the system returns the result based on the maximum length of the string to be processed.

If B is not used, all the remaining strings starting with a are obtained.

select substr('boatisgood',3,100) subs from dual;--atisgoodselect substr('boatisgood',3) subs from dual;--atisgoodselect substr('boatisgood',-3) subs from dual;--ood

12. Replace Functions

Select replace ('nba hupu pedestrian street has gone ', 'Pedestrian Street', 'bxj') from dual; -- why didn't nba hupu BXJ have gone?

 

What are common Oracle functions?

ABS (x) function, which is used to return the absolute value of a number.

ACOS (x) function returns the arc cosine of X. X ranges from 1 to-1, and the input value ranges from 0 to pie, in radians.

The ASIN (x) function returns the arc sine of X. X ranges from 1 to-1. The input value ranges from-PI/2 to PI/2, in radians.

The ATAN (x) function returns the arc tangent of X. The input value ranges from-PI/2 to PI/2 in radians.

BITAND (x, y) function returns the result of X and Y. X and Y must be non-negative integers. Note that there is no BITOR function, but it is useful for bitwise operators of RAW values in the UTL_RAW package.

The CEIL (x) function returns the smallest integer greater than or equal to X.

COS (x) function returns the cosine of x. X is an angle in radians.

COSH (x) function returns the hyperbolic cosine of X.

EXP (x) function, similar to power (x, y) function, returns the X power of E without specifying the base number. E = 71828183...

The FLOOR (x) function returns the largest integer less than or equal to X.

LN (x) function returns the natural logarithm of x. X must be greater than 0.

The LOG (x, y) function returns the base Y logarithm of X. The base must be a positive number not 0 or 1, and Y must be any positive number.

MOD (divisor, divisor) evaluates the remainder function. If the divisor is 0, the devisor is returned.

The POWER (x, y) function returns the power y of X. Neither the bottom X nor the exponent Y must be a positive integer, but if X is a negative number, Y must be an integer.

The ROUND (x [, y]) function returns the X value rounded to the Y-digit to the right of the decimal point. Y is 0 by default, which rounds X to the nearest integer. If Y is a negative number, round it to the corresponding digit on the left of the decimal point. Y must be an integer.

SIGN (x) function. This function is used to return positive and negative values of a number. If it is a positive number, 1 is returned. If it is a negative number,-1 is returned,
If the value is 0, the return value is still 0, which is a bit similar to the meaning of digitalization of analog.

SIN (x) function returns the sine of X. X is an angle in radians.

The SINH (x) function returns the hyperbolic sine of x.

The SQRT (x) function returns the square root of x. x cannot be a negative number.

The TAN (x) function returns the tangent of x. X is an angle in radians.

The TANH (x) function returns the hyperbolic tangent of x.

TRUNC (x [, y]) truncates the value function. The default value of Y is 0, so that X is truncated into an integer. If Y is negative, it is truncated to the right of the decimal point.

WIDTH_BUCKET (x, min, max, num_buckets) can only be used in SQL statements.

You can use WIDTH_BUCKET to create an equal-length segment based on input parameters. The range of MIN to MAX is divided into num_buckets sections, each of which has the same size. Returns the section where X is located. If X is less than MIN, 0 is returned. If X is greater than or equal to MAX, num_buckets + 1. MIN and MAX are returned.
Cannot be NULL, and num_buckets must be a positive integer. If X is NULL, NULL is returned.

Learning oracle

Comparison of common functions of SQL Server and Oracle

1. Absolute Value
S: select abs (-1) value
O: select abs (-1) value from dual

2. INTEGER (large)
S: select ceiling (-1.001) value
O: select ceil (-1.001) value from dual

3. Round (small)
S: select floor (-1.001) value
O: select floor (-1.001) value from dual

4. Round (truncation)
S: select cast (-1.002 as int) value
O: select trunc (-1.002) value from dual

5. Rounding
S: select round (1.23456, 4) value 1.23460
O: select round (1.23456, 4) value from dual 1.2346

6. e is the base power
S: select Exp (1) value 2.7182818284590451
O: select Exp (1) value from dual 2.71828182

7. Take the base logarithm of e.
S: select log (1, 2.7182818284590451) value 1
O: select ln (2.7182818284590451) value from dual; 1

8. Use 10 as the base logarithm.
S: select log10 (10) value 1
O: select log (10, 10) value from dual; 1

9. Square
S: select SQUARE (4) value 16
O: select power (4, 2) value from dual 16

10. Take the square root
S: select SQRT (4) value 2
O: select SQRT (4) value from dual 2

11. Evaluate the base power of any number
S: select power (3, 4) value 81
O: select power (3, 4) value from dual 81

12. Random Number acquisition
S: select rand () value
O: select sys. dbms_random.value (0, 1) value from dual;

13. Get the symbol
S: select sign (-8) value-1
O: select sign (-8) value from dual-1
---------- Mathematical functions

14. Circumference Rate
S: select pi () value 3.1415926535897931
O: Unknown

15. sin, cos, and tan parameters are in radians.
For example, select sin (PI ()/2) value to get 1 (SQLServer)

16. Asin, Acos, Atan, Atan2 return radians

17. radian angle Interchange (, Oracle unknown)
DEGREES: Radian-> Angle
RADIANS: Angle-> radian

--------- Comparison of Values

18. Calculate the maximum value of the Set
S: select max (value) value from
(Select 1 value... the remaining full text>