Shulou(Shulou.com)05/31 Report--

This article will explain in detail what the functions of T-SQL are, the editor thinks it is very practical, so share it with you for reference. I hope you can get something after reading this article.

1. Mathematical function:

Function name

Function action

Abs calculates the absolute value

1rand

Get 0-1 random number

2round

Round to keep decimal places

3square

Square operation

4power

Power operation 5sqrt

Square root operation

6pi

Pi 7ceiling

Rounding up

8floor rounded down 9

Ceiling (numeric_expression)

Returns the smallest integer greater than or equal to the given numeric expression (rounded up)

Floor (numeric_expression)

Returns the largest integer less than or equal to the given numeric expression (rounded down)

Round (numeric_expression, length)

Rounds the given data to the given length

PI ()

Constant 3.14159265358979

Rand ([seed])

Returns a random float value between 0 and 1

2. String function:

Str

Convert a floating-point number to the string left

Intercept the left string right intercept the right string space

Generate the space string lower/upper

Convert to lowercase / uppercase string reverse

Reverse order string len

Get string length replicate

Repeatedly generate the string rtrim

Clear the space on the right, substring

Intercept string

Ltrim (char_expr)

Delete the space before the string

Rtrim (char_expr)

Delete the space after the string

Left (char_expr, integer_expr)

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

Right (char_expr, integer_expr)

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

Substring (expr, start, length)

Returns the part of the specified expression that starts with the length bit length from the start position

3. Date function:

Getdate

Get the current date

Year get year month get month day

Get the number of days datepart

Get any time value datename

Get the character dateadd at any time

Date addition

Datediff

Whether date subtraction isdate is date data

4. Conversion function:

Using the cast function, you can cast one data type to another.

Cast (expression AS data_type)

The convert function allows users to convert expressions from one data type to another, as well as between different display formats of dates.

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

The style parameter, which provides a variety of date display formats. (this style is generally used when converting time type (datetime,smalldatetime) to string type (nchar,nvarchar,char,varchar).

5. Custom function:

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

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

Returns returns data type

Begin

Function content

Return expression

End

(2) the basic syntax for calling user-defined functions:

Variable = user name. Function name (actual argument list)

(3) process control statement:

When programming with T-SQL statements, it is often necessary to use a variety of process control statements for sequence, branch control transfer, loop and other 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

Define statement blocks:

Begin.end is used to specify a block of statements. All programs between begin and end belong to the same process control and are usually used together with if...else or while. In practical application, begin and end must appear in pairs. If there is only one line of program in the middle of begin...end, you can omit begin and end

If else statement:

Syntax format:

If Boolean expression

T-SQL statement | statement block

Else

If Boolean expression

T-SQL statement | statement block

In if statements, only one statement is required; multiple statements can be treated as logical statements with begin...end; clauses after if and else are allowed to be nested, and the nesting level 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 is the end of this article on "what are the functions of T-SQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.