The function of SQL learning T-programming

Here, we introduce the functions of T-SQL, such as mathematical functions, string functions, conversion functions, date functions, and custom functions.

1. Mathematical Functions:

Name of function	function action
Abs	Calculate absolute Value	1
Rand	Get 0-1 random numbers	2
Round	Rounding Reserved decimal digits	3
Square	Square operation	4
Power	Power operation	5
sqrt	Square root operation	6
Pi	Pi	7
Ceiling	Rounding up	8
Floor	Rounding down	9

Ceiling (numeric_expression)

Returns the smallest integer greater than or equal to the given number expression (rounding up)

Floor (numeric_expression)

Returns the largest integer less than or equal to the given number expression (rounding down)

Round (numeric_expression, length)

Rounds a given amount of data to a given length

PI ()

Constant 3.14159265358979

RAND ([seed])

Returns a random float value between 0 and 1

2. String function:

Str	Convert floating-point numbers to strings
Left	Truncate left string
Right	Intercept Right string
Space	Generate a space string
Lower/upper	Convert to small/uppercase string
Reverse	Reverse order string
Len	Get string length
Replicate	Duplicate string generation
RTrim	Clear the right space
Substring	Intercept string

LTrim (char_expr)

Delete the spaces in front of the string

RTrim (char_expr)

Delete the space following the string

Left (char_expr, integer_expr)

Returns the specified number of characters from the left in a string

Right (char_expr, integer_expr)

Returns the specified number of characters from the right start in a string

SUBSTRING (expr, start, length)

Returns the portion of the length from the start position in the specified expression.

3. Date function:

GetDate	Get Current date
Year	Get year
Month	Get month
Day	Get days
DatePart	Get any time value
Datename	Get any time character
DateAdd	Date addition
DateDiff	Date Subtraction
IsDate	is the date data

4. Conversion function:

Use the cast function to cast a data type to another data type

Cast (expression as data_type)

The CONVERT function allows the user to convert an expression from one data type to another, and also to convert between different display formats of the date.

Convert (data_type[(length)],expression[,style])

The style parameter, which provides a variety of date display formats (this style is typically used when the time type (Datetime,smalldatetime) and the string type (Nchar,nvarchar,char,varchar) are converted to each other.

5. Custom functions:

(1) The syntax for creating a user-defined function is as follows:

Create function function name (formal parameter name as data type)

returns Return Data type

Begin

function contents

return An expression

End

(2) Basic syntax for invoking user-defined functions:

variable = user name. function name (actual argument list)

(3) Process Control statement:

When programming with T-SQL statements, it is often useful to use various process control statements to sequence, branch control transfer, and loop operations. T-SQL provides a set of process control statements, including conditional control statements, unconditional control statements, loop statements, and statements that return status values

To define a statement block:

Begin......end is used to specify a block of statements, where the program between Begin and end belongs to the same process control, and is usually used in conjunction with If...else or while. In practice, begin and end must appear in pairs. If there is only one line in the middle of the begin...end, begin and end can be omitted

If Else statement:

Syntax format:

An If Boolean expression

T-SQL statement | statement block

Else

An If Boolean expression

T-SQL statement | statement block

In an if statement, only one statement is required, and multiple statements can be treated as a logical statement with Begin...end, and the clauses following the IF and else are allowed to be nested, and the nesting hierarchy is unrestricted

If exists statement:

Use student

if exists

(

SELECT *

From student

where sno= ' 0611101 '

)

print ' The student exists '

Else

print ' The student does not exist '

This article from "A Growing small Tree" blog, reproduced please contact the author!

The function of SQL learning T-programming