Functions, string functions, date functions, mathematical functions, system functions, aggregate Functions

------------------ String function ----------------------
-- Charindex (the string to be searched, the string to be searched, and the position to start searching): returns the position of the string to be searched in the string to be searched.
Select charindex ('sve', 'Hello SVE Hi sve', 1) -- The result is 7.
Select charindex ('sve', 'Hello SVE Hi sve', 8) -- The result is 14.
-- Len (string): returns the length of the string.
Select Len ('Hello sve ')
-- Lower (string): converts all letters to lowercase.
Select lower ('abc ')
-- Ltrim (string): removes the left space rtrim (string): removes the right space
Select ltrim ('abc ')
-- Right (string, number of characters to be obtained)
Select right ('abcdef', 2)
-- Replace (string, the character to be replaced, the character to be replaced)
Select Replace ('abcabcc', 'AB', 'ww ')
-- Stuff (string, the start position of the string to be deleted, the number of characters to be deleted, and the string to be inserted)
Select stuff ('abcdefg', 3, 2, 'hao ')

--------------- Date function ------------------
-- Getdate (): Get the current time
Select getdate ()
-- Dateadd (date part, value to be added, date): add the corresponding value according to the specified date part.
Select dateadd (mm, 2, '2014/1/1') -- display 2013/2
Select dateadd (YY,-2, '2014/1 ') -- display 2013/2
-- Datediff (date part, date 1, date 2): Calculate the gap between two dates based on the date part (I .e., date 2-date 1)
Select datediff (mm, '2014/1/1', '2014/3/5') -- display 5
Select datediff (mm, '2014/1/5', '2014/3/1') -- show-5
-- Datename (date part, date): obtains the string format of the specified date part.
Select datename (DW, '2014/1/6 ')
-- Datepart (date part, date): obtains the integer form of the specified date part.
Select datepart (DW, '2014/1/6 ')

-------------- Mathematical functions ----------------
-- Round (value, accuracy): returns the value to the nearest integer Based on accuracy.
Select round (43.125, 1) -- 43.100
Select round (43.125, 2) -- 43.130
-- Sign (value): determines whether the value is positive, negative, or 0. If it is positive, + 1 is returned. If it is negative,-1 and 0 are returned.
Select sign (10)
-- Rand (): generates a random number.
Select rand () -- generate 0 ~ Random Number of 1 (the system time is used as the seed by default)
Select rand (50) -- generate a fixed value (seed is set to a fixed 50)

---------------- System functions -----------------------
-- Convert (target data type, which must be converted): Data Type Conversion
Select 1 + 2 -- addition operation
Select '1' + '2' -- character connection
Select '1' + 2 -- display 3
Select '1' + convert (varchar, 2) -- display 12
-- Datalength (expression): calculated expression occupies several bytes
Select datalength ('AB') -- display 2
Select datalength (' ') -- display 4

---------------- Aggregate functions ---------------------
Select sum (score) from stuscore -- calculate the total score
Select sum (score) from stuscore where score> = 60 -- sum of scores for passing
Select max (score) from stuscore -- maximum value
Select min (score) from stuscore -- Minimum value
Select AVG (score) from stuscore -- average value
-- Count (parameter): The parameter can be * or a column name.
Select count (*) from stuscore -- get the number of rows in the result set
-- Note: Count (*) is used for statistics regardless of whether the field is null or not.
Select count (Address) from stuinfo -- get the number of rows whose address field is not null

Functions, string functions, date functions, mathematical functions, system functions, aggregate Functions