Common T-SQL database function arrangement

1. Aggregate functions

(1) AVG
The function returns the average value of the group value. The null value is ignored.
Syntax AVG ([All | distinct] expression)
(2) max
Function returns the maximum value of an expression.
Syntax max ([All | distinct] expression)
(3) min
The function returns the minimum value of the expression.
Syntax min ([All | distinct] expression)
(4) sum
The function returns the sum of all values in the expression, or only the distinct value. Sum can only be used for numeric columns. The null value is ignored.
Syntax sum ([All | distinct] expression)
(5) count
Function return the number of items in the group.
Syntax count ({[All | distinct] expression] | *})

 

2. Time and date functions

(1) dateadd
The function returns a new datetime value based on a specified date.
Syntax dateadd (datepart, number, date)
Example: Select dateadd (Day, 21, pubdate) as timeframe from titles
(2) datepart
Returns an integer representing the specified date of a specified date.
Syntax datepart (datepart, date)
Example: Select datepart (month, getdate () as 'month number'
(3) Day
Returns an integer that represents the day of the specified date.
Syntax Day (date)
(4) getdate
Function function by the datetime value of Microsoft? SQL Server? The standard internal format returns the current system date and time
Syntax getdate ()
(5) getutcdate
The function returns the datetime value that represents the current UTC time (World Time Coordinate or Greenwich Mean Time. The current UTC time is set from the current local time and the time zone in the operating system of the computer that runs SQL Server.
Syntax getutcdate ()
(6) month
Returns an integer representing the specified date month.
Syntax month (date)
Example: select "month number" = month ('2014/1/123 ')
(7) year
Returns an integer that represents the year of a specified date.
Syntax year (date)

 

3. mathematical functions

(1) ABS
Returns the absolute value of a given numeric expression.
Syntax ABS (numeric_expression)
(2) Rand
The function returns a random float value between 0 and 1.
Syntax rand ([seed]): seed is an integer expression (tinyint, smallint, or INT) that gives the seed value or the starting value)
(3) round
Function returns a numeric expression rounded to the specified length or precision.
Syntax round (numeric_expression, length [, function])
(4) Floor
Function returns the maximum integer that is less than or equal to the given number and is less than or equal to the maximum integer of the given numeric expression.
Syntax floor (numeric_expression)
(5) Sign
The function returns the positive (+ 1), zero (0), or negative (-1) Numbers of the given expression.
Syntax sign (numeric_expression)
(6) Square
Function returns the square of a given expression.
Syntax: Square (float_expression)
Example

 

3. system functions

(1) cast and convert
The function explicitly converts a data type expression to another data type. Cast and convert provide similar functions
Syntax cast (expression as data_type)
Convert (data_type [(length)], expression [, style])
Example: declare @ myval decimal (5, 2)
Set @ myval = 193.57
Select cast (@ myval as varbinary (20) as decimal (10, 5 ))
-- Or, using convert
Select convert (decimal (10, 5), convert (varbinary (20), @ myval ))
(2) coalesce
The function returns the first non-empty expression in the parameter.
Syntax coalesce (expression [,... n])
Example: Select cast (coalesce (hourly_wage * 40*52, salary, Commission * num_sales) as money) as 'total salary 'from wages
(3) datalength
Function returns the number of bytes occupied by any expression.
Syntax datalength (expression)
(4) @ Error
The function returns an error of the last executed Transact-SQL statement. Code
Syntax @ Error
Example: If @ error = 547
Print "A Check constraint violation occurred"
(5) isdate
Function to determine whether the input expression is a valid date
Syntax: isdate (expression)
(6) isnull
The function replaces null with the specified replacement value.
Syntax isnull (check_expression, replacement_value)
(7) isnumeric
Function to determine whether the expression is a valid numeric type
Syntax: isnumeric (expression)
(8) nullif
Function returns a null value if two specified expressions are equal.
Syntax nullif (expression, expression)
(9) @ rowcount
The function returns the number of rows affected by the previous statement.
Syntax @ rowcount

 

4. cursor Functions

(1) @ cursor_rows
The function returns the number of qualified rows in the cursor that is last opened on the connection. To improve performance, Microsoft? SQL Server? You can asynchronously fill in large keysets and static cursors. You can call @ cursor_rows to determine that when it is called, the number of rows that match the cursor is retrieved.
Syntax @ cursor_rows
Example: Select @ cursor_rows
Declare authors_cursor cursor
Select au_lname from authors
Open authors_cursor
Fetch next from authors_cursor
Select @ cursor_rows
Close authors_cursor
Deallocate authors_cursor
(2) @ fetch_status
The function returns the status of the last cursor executed by the fetch statement, rather than the status of any cursor currently opened.
Syntax @ fetch_status
Example: declare employee_cursor cursor
Select lastname, firstname from northwind. DBO. Employees
Open employee_cursor
Fetch next from employee_cursor
While @ fetch_status = 0
Begin
Fetch next from employee_cursor
End
Close employee_cursor
Deallocate employee_cursor

 

5. Metadata Functions

(1) col_length
Function returns the definition length of a column (in bytes)
Syntax col_length ('table', 'column ')
(2) col_name
The function returns the name of the database column, which has the corresponding table ID and column ID.
Syntax: col_name (table_id, column_id)
(3) index_col
The function returns the index column name.
Syntax index_col ('table', index_id, key_id)
(3) db_name
Name of the database returned by the function
Syntax db_name (database_id)
(3) object_id
Function returns the ID of the database object.
Syntax object_id ('object ')
Example: Select object_id ('pubs .. Authors ')
(4) object_name
Function returns the Database Object Name
Syntax object_name (object_id)
(5) @ procid
The identifier (ID) of the stored procedure returned by the function)
Syntax @ procid
(6) typeproperty
The function returns information about the data type.
Syntax typeproperty (type, property)
Example: Select typeproperty ('tinyint', 'precision ')

 

5. String Functions

(1) ASCII
Function returns the ASCII code value of the leftmost character of a character expression.
Syntax ASCII (character_expression)
(2) Char
Function function converts int ASCII code to character strings.
Syntax char (integer_expression)
(3) charindex
The function returns the starting position of the specified expression in the string.
Syntax charindex (expression1, expression2 [, start_location])
Parameter expression1: an expression that contains the order of characters to be searched. Expression1 is a short character data type classification expression.
Expression2: an expression, usually a column used to search for a specified sequence. Expression2 belongs to the string data type classification.
(4) Difference
The function returns the soundex value difference between two character expressions with an integer.
Syntax difference (character_expression, character_expression)
(5) left
Function returns the specified number of characters starting from the left of the string.
Syntax left (character_expression, integer_expression)
(6) Len
The function returns the number of characters (not bytes) of a given string expression, excluding trailing spaces.
Syntax Len (string_expression)
(7) lower
Function converts uppercase data into lowercase data and returns a character expression
Syntax lower (character_expression)
(8) ltrim
The function deletes the starting space and returns a character expression.
Syntax ltrim (character_expression)
(9) nchar
The function is defined according to the Unicode Standard and returns Unicode characters with the given integer code.
Syntax nchar (integer_expression)
Example
(10) patindex
The function returns the starting position of the first occurrence of a pattern in a specified expression. If this pattern is not found in all valid text and character data types, zero is returned.
Syntax patindex ('% pattern %', expression)
(11) peplace
The function replaces all the second given string expressions in the first string expression with the third expression.
Syntax Replace ('string _ expression1', 'string _ expression2', 'string _ expression3 ')
(12) reverse
Function returns the inverse of a character expression.
Syntax reverse (character_expression)
(13) Right
Function returns the number of integer_expression characters starting from the right side of the string.
Syntax right (character_expression, integer_expression)
(14) rtrim
The function truncates all trailing spaces and returns a string.
Syntax rtrim (character_expression)
(15) soundex
Function returns a code (soundex) consisting of four characters to evaluate the similarity between two strings.
Syntax soundex (character_expression)