SQL Server Basic functions

server|sqlserver| function
1. String function
Length and analytical use

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