Common Oracle functions-character and mathematical functions, oracle mathematical functions

Common Oracle functions-character and mathematical functions, oracle mathematical functions

Various functions are often used in the development and use of oracle. This chapter summarizes simple strings and mathematical functions, which will be convenient to use in the future, it can also reduce the number of detours for those who are interested.

-- Common character-related functions

1. substr string Truncation

Substr (string, starting position and length of truncation)

Select substr ('abcdef', 1, 3) from dual

The start position of the truncation. Both 0 and 1 indicate that the start position of the truncation is the first character;

 

2. Find the position of the substring in instr.

Select instr ('abcfdgfdhd', 'fd ') from dual

Returns the position of the first substring.

 

3. | string connection

Select 'hello' | ', world' fromdual;

 

4. trim processing on both sides of the string

Trim is generally used to delete spaces on both sides of a string. It can also be used to delete specified characters on both sides of a string. Trim specifies that the deleted string can only be a single character.

1. trim () deletes the halfwidth spaces on both sides of the string.

2. ltrim () deletes the halfwidth space on the left of the string.

3. rtrim () deletes the halfwidth space on the right of the string.

4. trim ('character 1' from 'string 2') deletes the specified character 1 from both sides of string 2.

5. trim ([leading | trailing | both] trim_char from string) deletes the specified character trim_char from the String.

Leading: deletes a string from its header.

Trailing: deletes a string from its tail.

Both: Delete the string from both sides.

 

Select trim ('ffm') as name from dual;

Select ltrim ('ffm') as name from dual;

Select rtrim ('ffm') as name from dual;

 

Select trim (leading 'F' from 'ffmmff') from dual;

Select trim (trailing 'F' from 'ffmmff') from dual;

Select trim (both 'F' from 'ffmmff') from dual;

 

5. ascii returns the Ascii value of the first letter of the string.

Select ascii ('A') from dual

 

6. chr returns the letter corresponding to the ascii Value

Select chr (97) from dual

 

7. length: calculates the string length.

Select length ('ffm') from dual

8. initcap, lower, and upper)

Select lower ('ffm'), upper ('ffm'), initcap ('ffm') from dual;

 

9. Replace replacement characters

Replace ('string to be changed ', 'replaced string', 'replaced string ')

Select replace ('ffm', 'M', '000000') from dual;

 

10. Replace the specified string with translate.

TRANSLATE (string, from_str, to_str)

TRANSLATE Is a superset of the functions provided by REPLACE. Replace each character in from_str with a string after the corresponding character in to_str. If from_str is longer than to_str, extra characters in from_str instead of to_str will be deleted from the string because they do not have replacement characters. To_str cannot be blank.

Select translate ('ffm', 'M', '000000') from dual;

The returned result is ff1.

11. lpad [left filling] rpad [Right filling] (used to control the output format)

Select lpad ('ffm', 8, '=') s1, rpad ('ffm', 8, '=') s2 from dual;

 

-- Common numeric and mathematical functions

1. Take the integer function (ceil rounded up and floor rounded down)

Select ceil (66.6) N1, floor (66.6) N2 from dual;

 

2. power and square root (sqrt)

Select power (3, 2) N1, sqrt (9) N2from dual;

 

3. remainder (mod)

Select mod (9,5) from dual;

 

4. Return a fixed number of decimal places (round: rounding, trunc: truncation)

Select round (66.667, 2) N1, trunc (66.667, 2) N2 from dual;

 

5. sign of the return value (the positive value of sign is 1, and the negative value is-1)

Select sign (-32), sign (293) fromdual;

 

6. returns the absolute value of x ABS (x)

Select abs (3), ABS (-1) from dual;

 

7. Return the logarithm LOG (x, y) based on x)

8. returns the POWER (x, y) of x)

9. returns the square root of x SQRT (x)

SELECTLOG (10,100), POWER (), SQRT (4) from dual;