Using simple functions in Oracle

Source: Internet
Author: User
Tags acos natural logarithm

Using simple functions in Oracle

This chapter describes the built-in functions of Oracle databases. The function can accept zero or multiple parameters and return an output parameter. Two main types of parameters can be used in Oracle databases:

  • Single row function (single row function) single row function can only operate on one row at the same time, and return a row of output results for each input row. The example of a single-row function is CONCAT (x, y). The function is to add y to x and return the obtained string.

  • Aggregate functions (aggregate function) aggregate functions can operate on multiple rows at the same time, and return a row of output results. An example of an aggregate function is AVG (x). Its function is to return the average value of x, where x can be a column or an expression.

  1. Use a single row function. There are five types of single-row functions:

    1. Character functions. Allows you to operate on character strings.

    2. Numeric functions. Allows you to calculate numbers.

    3. Conversion Function. You can convert one database type to another

    4. Date functions. Date and time can be processed

    5. Regular Expression functions. You can use a regular expression to query data.

1.1-character Functions

Letter-to-letter columns

Function Description
ASCII (x) Returns the ASCII code of character x.
CHR (x) Returns the characters with the ASCII code x.
CONCAT (x, y) Add y to x and return the string as the result.
INITCAP (x) Converts the first letter of each word in x to uppercase and returns the processing result.
INSET (x, find_string [, start] [, occurentce]) Search for the find_string string in x and return the position of find_string. An optional start position can be provided to specify the function to start searching from this position. Similarly, you can specify an optional occurrence parameter to indicate the location where the find_string appears.
LENGTH (x) Returns the length of x string.
LOWER (x)  

1.2 numeric Functions

Numeric functions can be used for calculation. The numeric function can accept the input numeric parameters. These numbers can be from the numeric column of a table or an expression that returns numbers. The numeric function calculates the input numbers and returns a numerical result. An example of a numeric function is SQRT (x). This function returns the square root of x.

Function Description Example
ABS (x) Returns the absolute value of x.

ABS (10) = 10

ABS (-10) = 10

ACOS (x) Returns the arc cosine of x.

ACOS (1) = 0

ACOS (-1) = 1, 3.1415926

ASIN (x) Returns the arc sine of x. If you are interested, you can verify it on your own.
ATAN (x) Returns the arc tangent of x.
ATAN2 (x) Returns the arc tangent of x and y.
BITAND (x, y) Returns the result of bitwise AND (AND) operations on x AND y.
COS (x) Returns the cosine of x.
COSH (x) Returns the hyperbolic cosine of x.
CEIL (X) Returns the smallest integer greater than or equal to x.
EXP (X) Returns the x power of e, which is approximately 2.7.18.  
FLOOR (X) Returns the largest integer less than or equal to x.  
LOG (X, Y) Returns the base y logarithm of x.  
LN (x) Returns the natural logarithm of x.  
MOD (x, y) Returns the remainder of x divided by y.  
POWER (x, y) Returns the Power y of x.  
ROUND (x [, y]) Returns the result of rounded up x.  
SIGN (x) If x is a negative number,-1 is returned. If x is an integer, 1 is returned. If x is 0, 0 is returned.  
SIN (X) Returns the sine of x.  
SINH (x) Returns the hyperbolic sine function of x.  
SQRT (x) Returns the square root of x.  
TAN (x) Returns the tangent of x.  

1.3 conversion functions

Sometimes a value may need to be converted from one data type to another.

ASCIISTR (x) Convert x to an ASCII string
BIN_TO_NUM (x) Convert binary NUMBER x to NUMBER type
DECODE (x, search, result, default) Compare x with the value in search. If the value is equal, the result value is returned. Otherwise, the default value is returned.
TO_BINARY_DOUBLE (x) Converts x to a BINARY_DOUBLE type.
TO_BINARY_FLOAT (x) Convert x to a BINARY_FLOAT type
TO_BLOB (x) Convert x to a Binary Large Object (BLOB) type.
TO_CHAR (x [, format]) Converts x to a VARCHAR2 string. You can specify an optional parameter format to describe the format of x.
TO_CLOB (x) Converts x to a character-Type Large Object (CLOB.
TO_DATE (x [, format]) Convert x to a DATE type.
TO_TIMESTAMP (x) Converts string x to a TIMESTAMP type.

1.4 Regular Expression Functions

Regular Expressions contain many metacharacters. ^ Matches the start of a string: [5-8 ~ Between 8; $ can match the end of a string.

Common metacharacters in orcle

Metacharacters Description Example

\

It indicates that the character to be matched is a special character, constant

\ N match the linefeed

\ Match \

\ (MATCH (

\) Match)

^ Match the start position of a string  
$ Match the end of a string  
* Match the previous character 0 or multiple times Ba * rk can match brk, bark, baark
+ Match the previous string 1 This or multiple times Ba + rk can match bark, baark, etc.
? Match the first character 0 or 1 time  
{N} Match a character EXACTLY n times, Hob {2} it can match hobbit
{N, m} Match a character for at least n times and at most m times  
. Matches any single character other than null.  
(Pattern) Matches a specified subexpression.  
X | y Match x or y,  
{Abc} Match any character in abc  
[A-z] Match any character in the specified range  
[:] Specifies a character class that can match any character in the class

[: Alphanum:] can match characters 0-9, A-Z and a-z

[: Alpha:] can match character A-Z and a-z

[: Blank:] matching spaces and tab keys

[: Digit:] numbers 0-9 can be matched

[: Graph:] can match non-null characters

[: Lower:] can match lowercase letters

[=] Specify equivalence class  
\ N This is a reverse reference of the previous capture, where n is a positive integer.  
\ D Numeric characters  
\ D Non-numeric characters  
\ W Letter  
\ W Non-letter characters  
\ S White space characters  
\ S Non-blank characters  
\ Matches the start position of a string only.  
\ Z Only match the end position of the string  
*? Matches the previous pattern element 0 or multiple times  
+? Match the previous pattern element once or multiple times  
?? Matches the previous pattern element 0 times or  
Regular Expression Functions
Function Description
REGEXP_LIKE (x, pattern [, match_option]) Search for the regular expression defined in the pattern parameter from x. You can use match_option to modify the default matching options.
REGEXP_INSRT (x, pattern [, start [, occurence [, rerturn_option [, match_option])

Search for 'pattern' in 'X' and return the position of 'pattern. You can specify the following optional parameters:

Start: the start position. The default value is 1, which indicates the first character of x.

Occurrence indicates the position of the occurrence of pattern for the current time.

Return_option indicates the integer to be returned.

Match_option: Modify the default matching settings

REGEXP_REPLACE (x, pattern [, replace_string [, start [, occurrence [, match_option]) Search for pattern in x and replace it with replace_string. For other options, refer to the previous method.
 

Ii. Use Aggregate functions

Function Description
AVG (x) Returns the average value of x.
COUNT (x) Returns the number of rows that contain x.
MAX (x) Returns the maximum value of x.
MEDIA (x) Returns the median of x.
MIN (x) Returns the minimum value of x.
STDDEV (x) Returns the standard deviation of x.
SUM (x) Returns the sum of x.
VARIANCE (x) Returns the variance of x.

3. GROUP rows BY and use the HAVING clause to filter row groups. Having cannot be placed in a group. group by can be used together with having, but having must be used together with group.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.