SQL Server Common functions

Just do the usual part of the brief introduction, detailed content see T-SQL online books.

1, statistic function AVG, COUNT, max, Min, sum

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

2, Mathematical functions

SQRT (n) returns the square root of a number

Square (n) returns the squared of a number
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 reserved for decimals
ABS (N)
Sign (n), when n>0, returns 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 (), converts characters to ASCII, ASCII (' abc ') = 97
char (), ASCII code converted to character
Low (), Upper ()
STR (A,B,C) converts a number to a string. A, which is the string to convert. b is the length of the conversion, and C is the number of decimal digits. 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 whether to include. Returns the first occurrence of the position, no return 0
Patindex ('%pattern% ', expression) function as above, but use wildcard characters
Replicate (' char ', rep_time), repeating string
Reverse (char), invert string
Replace (str, strold, strnew) Replacement string
Space (n), producing n empty rows
Stuff (), SELECT stuff (' abcdef ', 2, 3, ' ijklmn ') = ' Aijklmnef ', 2 is the start position, 3 is the length of the character to be removed 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,) as title, ytd_sales from the 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 the addition to that part, numbers knows how much to add, and date specifies whose base to add. 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 entire function result is date2-date1
Datename (datepart, date) takes that part and returns the string.
DATEPART (datepart, date) takes a portion and returns an integer.
GETDATE () Current time

5, System functions

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

SQL Server Common functions