Oracle uses simple functions

This chapter describes the built-in functions for Oracle databases. The function can accept 0 or more parameters and return an output parameter. There are two main types of parameters that can be used in an Oracle database:

• The single-row function single-line function can operate on only one row, and returns a row of output for each row entered. An example of a single-line function is concat (x, y), whose function is to add Y to X and return the resulting string

• Aggregate functions (aggregate function) aggregate functions can also operate on multiple lines and return a row of output results. An example of an aggregation function is AVG (x), whose function is to return an average of x, where x can be a column, or an expression

1. Use a single-line function. There are 5 main types of single-line functions:

   1. Character functions. You can manipulate strings that are made up of characters

   2. numeric functions. The number can be calculated

   3. Conversion functions. One type of database can be converted to another database type

   4. Date function. Date and time can be processed

   5. The regular expression function. Regular expressions can be used when querying data

1.1 Character functions

List of character functions

Function	Description
ASCII (x)	Returns the ASCII code of the character X
CHR (x)	Returns the character of the ASCII code x
CONCAT (x, y)	Add y to X and return the resulting string as a 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])	Find the string find_string in X and return to the location where Find_string is located. An optional start position can be provided to specify that the function is to be searched from this position. Similarly, you can specify an optional occurrence parameter to indicate where the first occurrence of find_string should be returned
LENGTH (x)	Returns the length of the X string
LOWER (x)

1.2 Numeric functions

Numeric functions can be used for calculations. Numeric functions can accept input numeric parameters, which can come from a table's numeric columns, or an expression that results in a number. The numeric function calculates the numbers that are entered to return a numeric result. An example of a numeric function is sqrt (x), which 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 inverse cosine of x	ACOS (1) = 0 ACOS (-1) = 3.1415926
ASIN (x)	Return x's Inverse chord	These interested friends can be verified by themselves.
ATAN (x)	Returns the inverse tangent of X
ATAN2 (x)	Returns the inverse tangent of x and Y
Bitand (x, y)	Returns the result of a bitwise and (and) operation on X and Y
COS (x)	Returns the cosine of X
COSH (x)	The hyperbolic cosine function that returns x
Ceil (X)	Returns the smallest integer greater than or equal to X
EXP (X)	Returns the x power of E, which is approximately equal to 2.7.18
Floor (X)	Returns the largest integer less than or equal to X
LOG (x, y)	Returns the logarithm of the base y 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 Y power of X
ROUND (X[,y])	Returns the result of an X rounding
Sign (x)	Returns 1 if X is a negative number, if it is an integer, returns 1 if 0, returns 0
SIN (X)	Returns the sine function of X
SINH (x)	Double-curve sine function with return x
SQRT (x)	Returns the square root of X
TAN (x)	Returns the tangent function of X

1.3 Conversion functions

Sometimes you might want to convert a value from one data type to another.

ASCIISTR (x)	Convert x to an ASCII string
Bin_to_num (x)	Converts a binary number x to type #
DECODE (X,search,result,default)	Compares x to the value in search, returns the value of result if it is equal, or returns the default
To_binary_double (x)	Convert x to a binary_double type
To_binary_float (x)	Convert x to a binary_float type
To_blob (x)	Converts 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 form of X
To_clob (x)	Converts x to a single character large object (CLOB) type.
To_date (X[,format])	Converts x to a date type.
To_timestamp (x)	Converts a string x to a timestamp type

1.4 Regular-expression functions

A regular expression contains a number of metacharacters. ^ can match the beginning of a string: [5-8] can match a number between 5~8; $ can match the end of a string.

Meta-characters commonly used in orcle

Metacharacters	Description	Example
\	Indicates that the character to match is a special character, constant	\ nthe match line break \ \ match \ \ (Match ( \) match)
^	Match the starting position of the string
$	Match the end position of the string
*	Matches the preceding character 0 or more times	Ba*rk can match Brk,bark,baark
+	Match the preceding string 1 this or several times	Ba+rk can match bark, baark, etc.
?	Match the preceding character 0 or 1 times
N	Matches a character that happens to be n times,	Hob{2}it can match Hobbit
{N,m}	Matches a character at least n times, up to M times
.	Match any single character outside of NULL
(pattern)	Matches one of the specified sub-expressions
X|y	Match x or Y,
{ABC}	Match any one of the characters in ABC
[A-z]	Matches any one 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 characters A-Z and a-Z [: Blank:] can match the space and TAB key [:d Igit:] can match the number 0-9 [: Graph:] can match non-null characters [: Lower:] can match lowercase letters
[==]	Specifying equivalence classes
\ n	This is a reverse reference to the previous capture, where n is a positive integer
\d	numeric characters
\d	Non-numeric characters
\w	Alphabetic characters
\w	Non-alphabetic characters
\s	White space characters
\s	Non-whitespace characters
\a	Matches only the beginning of a string
\z	Match only the end position of the string
*?	Matches the preceding pattern element 0 or more times
+?	Matches the preceding pattern element 1 or more times
??	Matches the preceding pattern element 0 times or
Regular-expression functions
Function	Description
Regexp_like (X,pattern[,match_option])	Searches for regular expressions 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]])	Finds pattern in X and returns the position where the pattern is located. You can specify optional parameters: Start: The location where you started the search. The default is 1, which means the first character of X Occurrence description should return the position of the current first occurrence of pattern Return_option indicates what integer should be returned Match_option modifying the default matching settings
Regexp_replace (X,pattern[,replace_string[,start[,occurrence[,match_option]])	Look for pattern in X and replace it with replace_string. For other options, refer to the previous method

Ii. use of aggregate functions

avg (x)	Returns the average of x
count (x)
max (x)
media (x) br>
min (x)	Returns the minimum value of x
stddev (x)
sum (x)	returns x and
variance (x)

Third, group by to row group BY, use HAVING clause to filter row groups. Having had to be placed in group No. GROUP by may not be used with the having, but has to be used with group by.

This article is from the "Ah Cool blog source" blog, please make sure to keep this source http://aku28907.blog.51cto.com/5668513/1787618

Oracle uses simple functions