Several functions in Oracle dealing with the number of decimal places, taking decimal places, Oracle query functions

About several Oracle functions that handle the number of decimal places ()
1. Rounding of several decimals
Select Round (1.2345, 3) from dual;
Results: 1.235
2. Reserved two decimal places, only
Select Trunc (1.2345, 2) from dual;
Results: 1.23

Select Trunc (1.2399, 2) from dual;

Results: 1.23
3. Take an integer
Returns the largest integer greater than or equal to x:
Sql> Select Ceil (23.33) from dual;
Results: 24

Returns the largest integer equal to or less than x:
Sql> Select Floor (23.33) from dual;
Results: 23

Returns the x value of the Y-bit rounded to the right of the decimal point: Rcund (X,[y])
Sql> Select Round (23.33) from dual;
Results: 23

Returns the X value of the truncated to Y decimal places: Trunc (X,[y])
Sql> Select Trunc (23.33) from dual;
Results: 23

Formatting numbers

The following is number examples for the TO_CHAR function.

To_char (1210.73, ' 9999.9 ') would return ' 1210.7 '
To_char (1210.73, ' 9,999.99 ') would return ' 1,210.73 '
To_char (1210.73, ' $9,999.00 ') would return ' $1,210.73 '
To_char (+, ' 000099 ') would return ' 000021 '

To_char function Special Usage
To_char (sysdate, ' d ') Day of the week
To_char (sysdate, ' DD ') Day of the month
To_char (sysdate, ' DDD ') the first few days of each year
To_char (sysdate, ' WW ') the first few weeks of each year
To_char (sysdate, ' mm ') the first few months of each year
To_char (sysdate, ' Q ') the first quarter of each year
To_char (sysdate, ' yyyy ') year
For example, to find a time for the first day of the week can
Sql> Select To_char (to_date (' 20070101 ', ' yyyymmdd '), ' d ') from dual;

1.instr

In Oracle/plsql, the InStr function returns the position of the string to intercept in the source string.

The syntax is as follows: InStr (string1, string2 [, Start_position [, Nth_appearance]])

String1 the source string to find in this string.

String2 the string to find in the string1.

Start_position represents where string1 is starting to find. This parameter is optional if omitted by default to 1. The string index starts at 1. If this parameter is positive, it is retrieved from left to right, and if this parameter is negative, right-to-left, returns the starting index of the string to find in the source string.

Nth_appearance representative to find the first occurrence of the string2. This parameter is optional, and if omitted, the default is 1. If the system is negative, it will give an error.

Attention:

If String2 is not found in String1, the InStr function returns 0.

Apply to:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
To illustrate:

Select InStr (' abc ', ' A ') from dual; --Return 1
Select InStr (' abc ', ' BC ') from dual; --Return 2
Select InStr (' abc abc ', ' A ', up to) from dual; --Return 5
Select InStr (' abc ', ' BC ', -1,1) from dual; --Return 2
Select InStr (' abc ', ' d ') from dual; --Return 0

Note: This function can also be used to check if the String1 contains String2, if the return 0 means not included, otherwise the inclusion is represented.

Several functions in Oracle dealing with the number of decimal places, taking decimal places, Oracle query functions