SQL Server T-SQL functions

Source: Internet
Author: User
Tags add time first string square root

An aggregate function (statistical function)!!!!!

Number of Counts count ()

Select COUNT (*) from table name

Sum sum ()

Select SUM (column name) from table name

Averaging avg ()

Select AVG (column name) from table name

Max ()

Select Max (column name) from table name

Minimum min ()

Select min (column name) from table name

Two. Math function: Manipulate one data and return a result

Upper limit ceiling ()

Select Code,name,ceiling (price) from car; --The column name disappears after the value operation is used as an alias

Lower floor ()

Select Floor, from car

Absolute ABS ()

Rounding round (A, B)-- A is the required value or column, and B is the number of digits left after the decimal point

Select round (oil, 0) from car--the decimal place depends only on the number of digits followed by whether or not a rounding is required, not additive

Pi (),- -no need to add anything in parentheses

Round (PI (), 2)

Open square root SQRT ()

Squared, multiply yourself Square ()

Three. String Function!!!

Convert Capital Upper ()

Select Upper (pic) from car;

Convert lowercase lower ()

Go to left Space LTrim () --a function with no spaces on both sides

Go right space RTrim ()

Intercept substring (A, B, C)----a operation target object B index (index starting at 1) C number of truncation number of bits

Left (, b)---intercept the B-bit

Right (, B)---intercept the B-bit

Length len ()

Select Len (' aaaaaa '); return several lengths

Replaces replace (A, B, C)----a operation target object B to be replaced by the C to be replaced by

Select replace (' aaaaabbaaaaa ', ' BB ', ' haha '); replace the BB in the first string with the haha

Flip Reverse ()

Select reverse (' abc '); The result is CBA

Four conversion function --in database +: string concatenation, value type mathematical operation

1 convert (A, b)----a type B column/value to be converted to

convert (nvarchar), oil)

2 cast (A as B) ---- a column/value B type to convert to

Cast (oil as nvarchar (10))

Five. Time-Date function

Gets the current system time GetDate ()----sysdatetime () Gets the timestamp of the database service Selec T getdate ()

Date of year ()

month () --No seconds

Day ()

Select year (' 1999-1-1 ')

Select Month (GetDate ())

Determine if the date is correct isdate () Select IsDate (' 2000-2-31 ') ----return bit type , False is 0,true is 1

Add Time DateAdd (A, B, D)----a add what type (year,month,day) B plus how many C to who add

Select DATEADD (year,5, ' 2000-1-1 ')

Returns the day of the Week Datename (weekday, time)-The returned value is the same as the string datepart can return a few weeks, but the type of int is returned

Select Datename (Weekday, ' 2000-1-1 ')

Datename (week, time)--weeks

Datename (day, time)-the day of the month

Datename (DayOfYear, Time)--the first day of the year

SQL Server T-SQL functions

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.