Common SQL Server Functions

Common SQL Server Functions

1. Statistical functions AVG, Count, Max, Min, Sum

For most Parties, null rows are not counted. It can be used with distinct to remove duplicate rows. Can be grouped with group

2. mathematical functions

SQRT
Ceiling (n) returns the smallest integer greater than or equal to n.
Floor (N), returns the largest integer less than or equal to n
Round (m, n), rounding, n is the number of digits that keep decimal places
ABS (N)
Sign (N). If n> 0, 1, n = 0 is returned. 0, n <0,-1 is returned.
Pi (), 3. 1415 ....
Rand (), Rand (N), returns a random number between 0 and 1.
3. String Functions

ASCII (): converts a character to an ascii code. ASCII ('abc') = 97
char (), ASCII code to character
low (), upper ()
STR (A, B, C) to convert digits to strings. A is the string to be converted. B Indicates the length after conversion, and C indicates the number of decimal places. STR (123.456, 123.46) =
ltrim (), rtrim () removes spaces
left (N), right (N), substring (STR, start, length) truncates the string
charindex (Child string, parent string) to check whether the string contains the string. Returns the location where the first occurrence occurs. The 0
patindex ('% pattern %', expression) function is not returned. However, the wildcard
replicate ('Char ', rep_time) is used ), duplicate string
reverse (char), reverse string
Replace (STR, strold, strnew) replace string
space (N ), generate n empty rows
stuff (), select stuff ('abcdef', 2, 3, 'ijklmn ') = 'aijklmnef', 2 is the starting position, 3 is the length of characters to be deleted from the original string, and ijlmn is the string to be inserted.
3. type conversion function:

Cast, cast (expression as data_type), example:
Select substring (title, 1, 30) as title, ytd_sales from titles where cast (ytd_sales as char (20) Like '3%'
Convert (data_type, expression)
4. Date Functions

Day (), month (), Year ()
Dateadd (datepart, number, date), datapart specifies to add that part, number knows how much to add, and date specifies the base on which to add. Datepart values include year, quarter, month, dayofyear, day, week, hour, minute, and second. For example, dateadd (day, 1, getdate () tomorrow ())
Datediff (datepart, date1, date2). datapart is the same as above. The result of the entire function is date2-date1.
Take the part of datepart (date) and return the string.
Datepart (datepart, date) is a part and an integer is returned.
Getdate () Current Time
5. system functions

col_length ('tablename', 'colname')
col_name, select col_name (object_id ('ployees'), 1) = employeeid
datalength, example: datalenght ('abc') = 3, datalength (pub_name) -- column name
db_id