SQL Server Common functions

server| function

1, statistical functions AVG, COUNT, max, Min, sum

Most parties do not count rows with null values. You can work with distinct to remove duplicate rows. Can be grouped with GROUP by

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), rounded, n is the number of digits to keep decimal
ABS (N)
Sign (n), when n>0, returns to 1,n=0, returns 0,N<0, returns-1
PI (), 3.1415 ....
RAND (), rand (n), returns a random number between 0-1
3, String function

ASCII (), converting characters to ASCII, ASCII (' abc ') = 97
char (), ASCII convert to character
Low (), Upper ()
STR (A,B,C) converts the number to a string. A, is the string to convert. b is the length of the conversion, and C is the decimal digit. STR (123.456,8,2) = 123.46
LTrim (), RTrim () go to space
Left (n), right (n), substring (str, start,length) intercept string
CHARINDEX (substring, parent string) to find if it contains. Returns the first occurrence of the position, not returning 0
Patindex ('%pattern% ', expression) function ditto, but using wildcard characters
Replicate (' char ', rep_time), repeating string
Reverse (char), reversing string
Replace (str, strold, strnew) Replacement string
Space (n), resulting in n empty rows
Stuff (), SELECT stuff (' abcdef ', 2, 3, ' ijklmn ') = ' Aijklmnef ', 2 is the starting position, 3 is the character length to be removed from the original string, and the IJLMN is the string to insert.
3, type conversion function:

Cast, cast (expression as Data_type), Example:
SELECT SUBSTRING (title, 1,) as title, ytd_sales from titles WHERE CAST (ytd_sales as char) like ' 3% '
Convert (data_type, expression)
4, Date function

Day (), month (), year ()
DATEADD (datepart, number, date), Datapart specifies which part to add, numbers knows how much to add, and date specifies on whose basis. DatePart values include, year,quarter,month,dayofyear,day,week,hour,minute,second, such as Tomorrow DateAdd (day,1, GETDATE ())
DateDiff (DATEPART,DATE1,DATE2). Datapart is the same as above. The whole function turns out to be date2-date1.
Datename (datepart, date) takes that part and returns the string.
DATEPART (datepart, date) takes a part and returns an integer.
GETDATE () Current time
5, System function

Col_length (' tablename ', ' colname ')
Col_name, SELECT col_name (object_id (' Employees '), 1) = EmployeeID
Datalength, Example:datalenght (' abc ') =3, datalength (pub_name)--Column name
db_id