SQL Basic Functions Encyclopedia _mssql

SQL Server Basic functions
1. String function length and analysis
DATALENGTH (char_expr) returns the string contains the number of characters, but does not contain the trailing spaces
SUBSTRING (expression,start,length) Don't say much, take the substring
Right (char_expr,int_expr) returns the int_expr character on the right-hand side of the string
Character Manipulation class
Upper (char_expr) to uppercase
Lower (char_expr) to lowercase
Space (int_expr) generates int_expr spaces
Replicate (char_expr,int_expr) copy string int_expr times
Reverse (char_expr) inverted string
Stuff (CHAR_EXPR1,START,LENGTH,CHAR_EXPR2) The string char_expr1 from the
The length character of start begins with CHAR_EXPR2 instead
LTrim (char_expr) RTrim (char_expr) Remove space
ASCII (char) char (ASCII) two functions, taking ASCII code, taking characters according to ASCII
String Lookup
CHARINDEX (char_expr,expression) returns the starting position of char_expr
Patindex ("%pattern%", expression) returns the starting position of the specified pattern, otherwise 0
2. Mathematical functions
ABS (NUMERIC_EXPR) to find absolute value
Ceiling (numeric_expr) takes the smallest integer greater than or equal to the specified value
EXP (float_expr) index
Floor (numeric_expr) is less than or equal to the maximum integer worth specified
Pi () 3.1415926 .....
Power (Numeric_expr,power) returns power to the second party
RAND ([int_expr]) random number generator
Round (numeric_expr,int_expr) Ann int_expr specified precision rounding
Sign (INT_EXPR), based on positive numbers, 0, negative numbers, returns +1,0,-1
sqrt (float_expr) square root
3. Date function
GETDATE () Return date
Datename (datepart,date_expr) return name such as June
DATEPART (datepart,date_expr) take the date part
DateDiff (DATEPART,DATE_EXPR1.DATEEXPR2) Date Difference
DATEADD (datepart,number,date_expr) return date plus number
The datepart in the above function
The value and significance of the formulation
YY 1753-9,999 part
QQ 1-4 Moment
MM January-December
Dy 1-366 Days

DD 1-31 Days
WK 1-54 weeks
DW 1-7 weeks
HH 0-23 Hours
Mi 0-59 minutes
SS 0-59 sec
Ms 0-999 ms
Date conversion
Convert ()
4. System function
Suser_name () User login name
USER_NAME () The name of the user in the database
User's name in the database
Show_role () rules that work for the current user
Db_name () database name
object_name (obj_id) database object name
Col_name (obj_id,col_id) column name
Col_length (objname,colname) column length
Valid_name (char_expr) is a valid identifier