Common SQL Server Functions

Sqlserver Functions

1 string Function

1.1 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

1.2-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.

1.3 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