Introduction to basic Oracle Functions

Oracle functions are divided into basic functions and analysis functions,

The basic functions are the most commonly used,

It is rumored that it is often used in data warehouses,

In this blog post, we will only introduce the most common basic Oracle functions.

According to the following route,

Character Functions

Numeric Functions

Date Functions

Conversion functions

Group functions

Start with the character function.

1. ASCII (c)

C is a string parameter. The function is used to pass in strings,

Then return the ASCII value of the first letter of the string,

2. CHR (I)

Where I is a number, and then returns a decimal character, that is, the inverse function of ASCII (c ).

3. Concat (C1, C2)

This function has two string parameters. This function is used to concatenate two strings.

4. initcap (C1)

This function is special. It has a string parameter C1,

It is used to uppercase the first letter of each word in a string, while other letters are returned in lowercase,

The word contains spaces, control characters, and punctuation characters.

5. instr (C1, C2, I, j)

In this function, there are many parameters, including four. C1 and C2 are strings, while I and j are numbers,

The I and j parameters are optional, that is, the two parameters can be left blank, because both parameters have default values,

All are 1

This function returns the position where C2 appears for the first J in C1. In fact, this is a search process,

The start position of the search is determined by I, and I is determined to start matching from the I character in C1.

6. Length (c)

This function is obviously used, that is, to return the length of the string of the passed parameter C.

7. Lower (c)

The functions of this function are also obvious,

Is to convert all the passed parameters, that is, a string C, to lowercase, and then return

8. Upper (c)

This function is used to uppercase all the characters in the C string and return

9. lpad (C1, I, C2)

There are three parameters, c1 and c2 are strings, and I is a number,

If I is less than the length of C1, only the characters of C1 are returned,

Otherwise, use the C2 string on the left side of C1 to fill in the length I, which can be repeated multiple times,

The default value of C2 is single space. The length of the string returned by this function must be I

10. rpad (C1, I, C2)

This function is similar to the above functions, except that the above lpad function is supplemented from left to right,

This rpad is supplemented from right to left,

That is, when the value of I is smaller than the length of C1, C1 is intercepted directly and a string with the length of I is returned,

When the I value is greater than the length of C1, C2 is used to supplement C1 from right to left.

11. ltrim (C1, C2)

This function removes the leftmost character of C1 so that the first character is not in the string C2.

If you cannot understand it, make sure to compare the following results.

12. rtrim (C1, C2)

This function is similar to the function above, except that it is compared from the left side, but from the right side of the string C1,

So that the rightmost Letter of C1 is not in the string C2

13. Replace (C1, C2, C3)

This function has a special role. It uses C3 to replace C2 that appears in C1, and then returns the result.

14. substr (C1, I, j)

In the preceding parameter, C1 is a string, I and j are numbers, and J is optional,

Return the intercepted string with the length of J from the I-bid of C1. If the J-bits are empty, the string is returned until the end of C1.

To put it bluntly, it is the substring function in C #.

15. Translate (C1, C2, C3)

This function uses C3 to replace the same characters in C1 and C2, and then returns the result.

This function is difficult to explain, so we need to explain it as follows,

For example, the first letter in baobeime is the same as the first letter in Bei,

Therefore, replace the first letter in xza,

BEI and Bei in baobeime all match, so xza must be used together to replace Bei.

16. Trim (C1 from C2)

This function truncates a specific character from the header, tail, and end of the string,

C1 is the character to be truncated, and C2 is the source string

 

 

 

A digital Function

Here we talk about mathematical functions. To be honest, they are similar to the mathematical functions in math in C,

Note that the parameters and values of all trigonometric functions in Oracle are radians rather than degrees.

1. Abs (N)

What this function sees more is the absolute value of N.

2. Ceil (N)

Returns the smallest integer greater than or equal to n.

3. Floor (N)

Returns the maximum integer of <= N

4. Sin (N)

Returns the sine of degrees N.

5. Cos (N)

Returns the cosine of degrees N.

6. Tan (N)

Returns the tangent of degrees N.

7. exp (N)

Returns the N power of E, that is, e ^ n.

8. Power (N1, N2)

Returns the N2 power of N1, that is, N1 ^ N2.

9. Mod (N1, N2)

Returns the remainder of N1 divided by N2, that is, N1 % N2.

10. ln (N)

Returns the natural logarithm of N.

11. Log (N1, N2)

Returns the base-N1 logarithm of N2.

12. SQRT (N)

Returns the square root of N.

13. trunc (N1, N2)

Return the N1 value from the end of the truncation to the N2 decimal point. The default value of N2 is 0,

When N2 is set as the default value, the end of N1 is taken as an integer. If N2 is negative,

It is truncated at the right of the decimal point.

14. Round (N1, N2)

Returns the N1 value of N2 at the right of the decimal point. The default value of N2 is 0.

15. Sign (N)

If n is less than 0,-1 is returned.

If n> 0, 1 is returned.

If n = 0, 0 is returned.

 

 

Then the date function

Since it is a date function, it must be a date-type data operation,

1. sysdate

This function is special and has no parameters, so no brackets or the like are required,

It's okay to use it naked. The function is used to return the current time and date.

2. add_months (D, I)

Returns the result of date D plus I months. I can be any integer.

3. last_day (d)

Returns the last day of the month where date D is located.

4. months_between (D1, D2)

Returns the number of months between the date D1 and the date D2.

 

5. trunc (D, Format)

Returns the date D of the unit specified by format.

For example, if you can specify format as Y, it indicates that the Year of the specified date is returned (in years)

If the specified format is mm, it indicates that the month of the specified date is returned (in the unit of month)

If the specified format is D, it indicates that the day of the specified date is returned (in days)

The Conversion Function

Conversion Function, used for conversion between multiple data types

1. rowidtochar (rowid)

This function is used to convert the rowid parameter of the rowid type to the string type.

2. chartorowid (c)

This function is opposite to the previous rowidtochar function. It is used to convert string C to the rowid data type.

3. to_char (x, Format)

This function is used to convert a parameter X to a string type,

X can be of the number or date type.

4. to_date (C, Format)

Convert string to date data type

5. to_number (c)

Converts a string parameter to a number data type.

Then composite Functions

This is more common, such as Count (), AVG (), max (), and min ().

I will not discuss this much.