SQL function library

1. String functions:

Datalength (char_expr) returns the number of characters in the string, but does not contain spaces
Length (expression, variable) specifies the length of the string or variable name.
Substring (expression, start, length ).
Right (char_expr, int_expr) returns int_expr characters to the right of the string
Concat (str1, str2,...) returns a string from the parameter link. Datalength (char_expr) returns the number of characters in the string, but does not contain spaces
Length (expression, variable) specifies the length of the string or variable name.
Substring (expression, start, length ).
Right (char_expr, int_expr) returns int_expr characters to the right of the string
Concat (str1, str2,...) returns a string from the parameter link.

Character operation class:

Convert upper (char_expr) to uppercase
Lower (char_expr) to lowercase
Ucase (string) returns variant (string), which contains a string converted to uppercase.
Lcase (string) returns the string in lowercase.
Space (int_expr) generates int_expr Spaces
Replicate (char_expr, int_expr) copies the string int_expr times
Reverse (char_expr) reverse string
Stuff (char_expr1, start, length, char_expr2) Replace the length characters starting from start in character char_expr1 with char_expr2
Ltrim (char_expr) rtrim (char_expr) removes Spaces
The ASCII (char) Char (ASCII) function corresponds to each other, and the ASCII code is used.

String SEARCH:

Charindex (char_expr, expression) returns the starting position of char_expr
Patindex ("% pattern %", expression) returns the starting position of the specified mode. Otherwise, it is 0.
Locate (substr, STR, POS) returns the position of the substring substr In the first occurrence of the substring Str.

2. mathematical functions

ABS (numeric_expr) returns the absolute value
Ceiling (numeric_expr) is the smallest integer greater than or equal to the specified value.
Exp (float_expr) returns an index
Floor (numeric_expr) is less than or equal to the value of the specified maximum integer
Power (numeric_expr, power) returns power to power
Rand ([int_expr]) random number generator
Round (numeric_expr, int_expr) the precision specified by anint_expr is rounded
Sign (int_expr) returns + 1, 0,-1 based on positive number, 0, negative number.
SQRT (float_expr) Square Root
Exp (float X): Evaluate the X power of e
Tan (float X): calculates the tangent of X (expressed in radians.
Atan (float X): returns the arc tangent of X (expressed in radians ).
Cos (float X): returns the cosine of X (expressed in radians ).
ACOs (float X): returns the arc cosine of X (expressed in radians ).
Sin (float X): calculates the sine of X (expressed in radians.
Asin (float X): returns the arc sine of X (expressed in radians ).
FABS (float X): returns the absolute value of floating point number X.
Fmod (float X, float y): calculates the remainder of x/y.
Pow (float X, float y): Calculate the Y Power of X.
SQRT (float X): calculates the square root of X.

3. Date and Time Functions

Getdate () returns the date
Datename (datepart, date_expr) returns the name
Datepart (datepart, date_expr) is a part of the date.
Datediff (datepart, date_expr1.dateexpr2) date difference
Dateadd (datepart, number, date_expr)

4. system functions

Suser_name () User Login Name
User_name () name of the user in the database
User name in the database
The rule that show_role () applies to the current user.
Db_name () Database Name
Object_name (obj_id) Database Object Name
Col_name (obj_id, col_id) column name
Col_length (objname, colname) Column Length
Whether valid_name (char_expr) is a valid identifier

5. SQL Aggregate functions:

AVG function: calculates the arithmetic mean value of a group of values contained in a specific query field.

Syntax: AVG (expr) expr represents a string expression, or identifies a field that contains the data to calculate the average value; or identifies an expression, it uses the data in this field for calculation.

Note: The average value calculated using AVG is the arithmetic average value (divide the sum of all values by the number of values ). The AVG function does not include any null field in calculation. You can use min and Max to Query expressions and querydef objects with SQL features, or when creating a recordset object based on SQL queries.

Count function: calculates the number of records returned from the query.
 

Syntax: Count (expr) expr represents a string expression, which identifies a field that contains the data to be calculated, or an expression that uses the data in this field for computation. The calculation object in expr may include a table field name, a constant or a function (which may be internal or user-defined, but not an SQL aggregate function ). you can compute data of any type, including text data.
 

Note: You can use count to calculate the number of records for the next-level query. However, count only calculates the number of records. It does not matter what value is saved in the record. The count function does not count records with null fields. If expr identifies multiple fields, the count function calculates only one record if the value of at least one field is not null. If all specified fields are null, no records are calculated. Use (&) to separate field names. You can use count in the query expression. You can apply these expressions to querydef objects with SQL features or when creating a recordset based on SQL queries.

First and last functions: In the result set returned by the query, the field values returned by the first record or the last record.

Syntax: First (expr); last (expr) expr represents a string expression, which identifies a field that contains the data to be calculated; or an expression, it uses the data in this field for calculation. The calculation object in expr may include a table field name, a constant or a function (which may be internal or user-defined, but not an SQL aggregate function ).
 

Note: The first and last functions are similar to the movefirst and movelast methods in the DaO record set objects. They only return the specified field values of the first or last record in the query result set. The records returned by these functions are usually not ordered unless the query contains an order by clause.

Min and Max functions: return the minimum or maximum value from the value of a specified field in a query.

Syntax: min (expr); max (expr) expr represents a string expression, which identifies a field that contains the data to be calculated; or an expression, it uses the data in this field for calculation. The calculation object in expr may include a table field name, a constant or a function (which may be internal or user-defined, but not an SQL aggregate function ).

Note: min and Max can be used to calculate the minimum and maximum values of a field. This field is based on the specified sum or group. For example, you can use these functions to return the lowest and highest freight costs. If no aggregate is specified, the entire table is used. You can use AVG to Query expressions and querydef objects with SQL features, or create a recordset object based on SQL queries.

STDev and stdevp functions: return the estimated value of the standard deviation of the population or population sample, which is expressed by a group of values contained in a specified field of a query.
 

Syntax: STDev (expr); stdevp (expr) expr represents a string expression, which can identify a field that contains the data to be calculated; or an expression, it uses the data in this field for calculation. The calculation object in expr may include a table field name, a constant or a function (which may be internal or user-defined, but not an SQL aggregate function ).
 

Note: The stdevp function calculates the population, while the STDev function calculates the population. If the next-level query contains two or less records (or no records, for the stdevp function), the two functions return a null value (indicating that the standard deviation cannot be calculated ). You can use STDev and stdevp functions in the query expression. You can apply these expressions to querydef objects with SQL features or when creating a recordset based on SQL queries.

Sum function: returns the total of a group of values contained in a specified query field.

Syntax: sum (expr) expr represents a string expression, which identifies a field that contains the data to be calculated, or an expression that uses the data in this field for computation. The calculation object in expr may include a table field name, a constant or a function (which may be internal or user-defined, but not an SQL aggregate function ).
 

Description: sum function calculates the sum of the values of a field. For example, you can use the sum function to calculate the total freight charges. The sum function ignores records that contain null fields. The following example shows how to calculate the total number of unitprice and quantity fields of a product:
Select
Sum (unitprice * quantity)
As [total revenue] from [Order Details];
You can use the sum function in a query expression. You can also use this expression when creating SQL-based recordset objects in querydef objects with SQL features.

VaR and varp functions: return an estimate of the variance of a population or population sample, which is expressed by a group of values included in the specified query field.

Syntax: VAR (expr); varp (expr) expr represents a string expression, or identifies a field, which contains the data to be calculated; or an expression, it uses the data in this field for calculation. The calculation object in expr may include a table field name, a constant or a function (which may be internal or user-defined, but not an SQL aggregate function ).

Note: The varp function is used to calculate the population, while the VaR function calculates the population sample. If the next-level query contains two or less records, the VaR and varp functions return a null value, which indicates that the variance cannot be calculated. You can use the VaR and varp functions in a query expression or SQL statement.

 

1. String Functions

Length and Analysis

Datalength (char_expr) returns the number of characters in the string, but does not contain spaces

Substring (expression, start, length ).

Right (char_expr, int_expr) returns int_expr characters to the right of the string

Character operation class

Convert upper (char_expr) to uppercase

Lower (char_expr) to lowercase

Space (int_expr) generates int_expr Spaces

Replicate (char_expr, int_expr) copies the string int_expr times

Reverse (char_expr) reverse string

Stuff (char_expr1, start, length, char_expr2)

The length starting with start is replaced by char_expr2.

Ltrim (char_expr) rtrim (char_expr) removes Spaces

The ASCII (char) Char (ASCII) function corresponds to each other, and the ASCII code is used.

String search

Charindex (char_expr, expression) returns the starting position of char_expr

Patindex ("% pattern %", expression) returns the starting position of the specified mode. Otherwise, it is 0.

2. mathematical functions

ABS (numeric_expr) returns the absolute value

Ceiling (numeric_expr) is the smallest integer greater than or equal to the specified value.

Exp (float_expr) returns an index

Floor (numeric_expr) is less than or equal to the value of the specified maximum integer

Pi () 1415926 .........

Power (numeric_expr, power) returns power to power

Rand ([int_expr]) random number generator

Round (numeric_expr, int_expr) the precision specified by anint_expr is rounded

Sign (int_expr) returns + 1, 0,-1 based on positive number, 0, negative number.

SQRT (float_expr) Square Root

3. Date Functions

Getdate () returns the date

Datename (datepart, date_expr) returns a name such as June

Datepart (datepart, date_expr) is a part of the date.

Datediff (datepart, date_expr1.dateexpr2) date difference

Dateadd (datepart, number, date_expr)

In the preceding functions

Value and meaning of writing

YY 1753-March

QQ: 1-4

Mm 1-March December

Dy 1-366

DD 1-31

WK 1-54 weeks

DW 1-7 weeks

HH 0-23 hours

Mi 0-59 minutes

SS 0-59 seconds

MS 0-999 Ms

Date Conversion

Convert ()

4. system functions

Suser_name () User Login Name

User_name () name of the user in the database

User name in the database

The rule that show_role () applies to the current user.

Db_name () Database Name

Object_name (obj_id) Database Object Name

Col_name (obj_id, col_id) column name

Col_length (objname, colname) Column Length

Whether valid_name (char_expr) is a valid identifier