SQL Server basic functions
1. String function length and Analysis
Datalength (Char_expr) returns the number of characters in the string, but does not contain spaces
Substring (expression, start, length ).
Right (char_expr, int_expr) returns int_expr characters to the right of the string
Character operation class
Convert upper (char_expr) to uppercase
Lower (char_expr) to lowercase
Space (int_expr) generates int_expr Spaces
Replicate (char_expr, int_expr) copies the string int_expr times
Reverse (char_expr) reverse string
Stuff (char_expr1, start, length, char_expr2)
The length starting with start is replaced by char_expr2.
Ltrim (char_expr) rtrim (char_expr) removes Spaces
The ascii (char) char (ascii) function corresponds to each other, and the ascii code is used.
String search
Charindex (char_expr, expression) returns the starting position of char_expr
Patindex ("% pattern %", expression) returns the starting position of the specified mode. Otherwise, it is 0.
2. mathematical functions
Abs (numeric_expr) returns the absolute value
Ceiling (numeric_expr) is the smallest integer greater than or equal to the specified value.
Exp (float_expr) returns an index
Floor (numeric_expr) is less than or equal to the value of the specified maximum integer
Pi () 1415926 .........
Power (numeric_expr, power) returns power to power
Rand ([int_expr]) random number generator
Round (numeric_expr, int_expr) the precision specified by anint_expr is rounded
Sign (int_expr) returns + 1, 0,-1 based on positive number, 0, negative number.
Sqrt (float_expr) Square Root
3. Date Functions
Getdate () returns the date
Datename (datepart, date_expr) returns a name such as June
Datepart (datepart, date_expr) is a part of the date.
Datediff (datepart, date_expr1.dateexpr2) date difference
Dateadd (datepart, number, date_expr)
In the preceding functions
Value and meaning of writing
Yy 1753-March
Qq: 1-4
Mm 1-March December
Dy 1-366
Dd 1-31
Wk 1-54 weeks
Dw 1-7 weeks
Hh 0-23 hours
Mi 0-59 minutes
Ss 0-59 seconds
MS 0-999 Ms
Date Conversion
Convert ()
4. system functions
Suser_name () User Login Name
User_name () name of the user in the database
User name in the database
The rule that show_role () applies to 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
Whether valid_name (char_expr) is a valid identifier
