SQL Server Functions Daquan

SQL2008 expression: Is any combination of constants, variables, columns, or functions, and operators.

1. String functions

Function Name Parameter Example description

ASCII (string expression)

Select ASCII (' ABC ') returns 97

Returns the ASCII code of the leftmost character in the string.

Char (An integer expression)

Select char (100) returns D

Converts the ASCII code to a character.

An integer between 0 and 255. If the integer expression is not within this range, it returns

NULL value.

charindex (String expression 1, string-expression 2[, integer-expression])

Select CHARINDEX (' ab ', ' Bcabtabd ') returns 3

Select CHARINDEX (' ab ', ' bcabtabd ', 4) returns 6

In string 2, look for the string 1 if there is a return of the first matching position if there is no return 0. Returns null if there is a null in string 1 and string 2. Can be specified in a string

The starting position of the lookup in 2.

difference (String-expression 1, string-expression 2)

Select difference (' Green ', ' Greene ') returns 4

Returns an integer value from 0 through 4 that indicates how similar the two-character expression is to each other.

0 means almost different or completely different,

4 means almost the same or exactly the same.

Note that similarity does not mean equal

Left (string-expression, integer-expression)

Select Left (' ABCDEFG ', 2) returns AB

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

Right (string-expression, integer-expression)

Select Right (' ABCDEFG ', 2) returns FG

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

Len (string expression)

Select Len (' ABCDEFG ') returns 7

Select Len (' ABCDEFG

') returns 7

Returns the number of characters in the specified string expression that does not contain trailing spaces.

Lower (string expression)

Select lower (' ABCDEF ') returns ABCDEF

Returns a character expression with uppercase characters converted to lowercase.

Upper (string expression)

Select Upper (' abcdef ') returns ABCDEF

Returns a character expression that converts lowercase characters data to uppercase.

LTrim (string expression)

Select LTrim ('

ABC ') return ABC

Returns the character expression after the leading space has been deleted.

RTrim (string expression)

Select RTrim (' ABC

') return to ABC

Returns the character expression after trailing spaces have been deleted.

Patindex (String-expression 1, string-expression 2)

Select Patindex ('%ab% ', ' 123ab456 ') returns 4

Select Patindex (' ab% ', ' 123ab456 ') returns 0

Select Patindex (' ___ab% ', ' 123ab456 ') returns 1

Select Patindex (' ___ab_ ', ' 123ab456 ') returns 0

In a string expression

You can use a wildcard character in 1, the first of this string

Characters and the last character are usually

%。

% denotes any number of characters, _ denotes any character

Returns a string expression

2 The starting position of the first occurrence of the specified pattern in string expression 1. No return 0 found

Reverse (string expression)

Select reverse (' ABCDE ') returns EDCBA

Returns the new string after the specified string is reversed

Space (An integer expression)

Select ' A ' +space (2) + ' B ' returns a B

Returns a string consisting of a specified number of spaces.

Str

(Float type decimal [, total length [, decimal point

Number of digits retained after]])

Select STR (123.451) returns

123 (123 Front with spaces)

Select STR (123.451,3) returns 123

Select STR (123.451,7,3) returns 123.451

Select STR (123.451,7,1) returns 123.5

Select STR (123.451,5,3) returns 123.5

Select STR (123.651,2) return * *

Returns a string converted from a number. Returns the number of characters less than the total length of the front complement

A space that exceeds the total length of the truncated decimal place. If you need to truncate the integer digits, return

**。

Note that rounding is followed when truncating

Total length. It includes decimal points, symbols, numbers, and spaces. The default value is

10.

Most reserved after decimal point

16 bits. The number after the decimal point is not preserved by default

Stuff

(string expression 1, start position, length, string expression 2)

Select Stuff (' abcdef ', 2, 2, ' 123 ')

Back to A123def

In a string expression

1 removes the specified length of characters at the specified starting position,

and inserts a string expression at the beginning of the specified position

2. Returns a new string

substring (string expression, start position, length)

Select substring (' abcdef ', 2,2) return BC

return substring

Replace (String expression 1, string-expression 2, string-expression 3)

Select replace (' abcttabchhabc ', ' abc ', ' 123 ')

Back to 123tt123hh123

Using string expressions

3 Replace all string tables that appear in string expression 1

-Up

2 of matches. Returns a new string

2. Date and Time functions

Function name

Parameter Example Description

DATEADD (date part, number, date)

Select DATEADD (year,45, ' 1990-12-11 ')

Back to 2035-12-11 00:00:00.000

Select DATEADD (month,45, ' 1990-12-11 ')

Back to 1994-09-11 00:00:00.000

Select DATEADD (mm,45, ' 1990-12-11 ')

Back to 1994-09-11 00:00:00.000

Select DATEADD (qq,12, ' 1990-12-11 ')

Back to 1993-12-11 00:00:00.000

Select DATEADD (hh,12, ' 1990-12-11 ')

Back to 1990-12-11 12:00:00.000

Select DATEADD (yy,-12, ' 1990-12-11 ')

Back to 1978-12-11 00:00:00.000

Returns a new date value that is added to the specified date after a time interval.

Number: The value that is used to add to the specified date part. If a non-integer value is specified,

The decimal portion of the value is discarded, and rounding is not followed when it is discarded.

Date: The original date specified

In this function

The Dw,dy,dd effect represents the day

DateDiff (date part, start date, end date)

Select

DateDiff (yy, ' 1990-12-11 ', ' 2008-9-10 ')

Returns an integer value of the difference of the specified date portion of two specified date.

Subtract start date from end date at calculation

Returns 18

Selectdatediff (mm, ' 2007-12-11 ', ' 2008-9-10 ')

Returns 9

In this function

The Dw,dy,dd effect represents the day

Datename (date part, date)

Select Datename (mm, ' 2007-12-11 ') returns 12

Select Datename (DW, ' 2007-12-11 ') returns Tuesday

Select Datename (dd, ' 2007-12-11 ') returns 11

Returns a String that represents the specified date portion of the specified date.

DW represents the day of the week, WK the first few weeks of the year

DY denotes the day ordinal of a year

DatePart

(date part, date)

Select DATEPART (mm, ' 2007-12-11 ') returns 12

Select DATEPART (DW, ' 2007-12-11 ') returns 3

Select DATEPART (dd, ' 2007-12-11 ') returns 11

Returns an integer that represents the specified date portion of the specified date.

WK represents the week ordinal of a year

DY represents the day of the year,

DW represents the day of the week and returns an integer default of 1 for Sunday

GETDATE No parameters

Select GETDATE ()

Back to 2009-04-28 18:57:24.153

Returns the current system date and time.

Day

Date

Select Day (' 2007-12-11 ') returns 11

Returns an Integer that represents the part of the day of the specified date.

Equivalent to

DATEPART (dd, date)

Month

Date

Select month (' 2007-12-11 ') returns 12

Returns an Integer that represents the part of the month of the specified date.

Equivalent to

DatePart (mm, date)

Year

Date

Select year (' 2007-12-11 ') returns 2007

Returns an Integer that represents the part of the year of the specified date.

Equivalent to

DatePart (yy, date)

getUTCDate

No parameters

Select getUTCDate ()

Back to 2009-04-28 10:57:24.153

Returns a representation of the current

UTC (World Standard Time) time. That's Greenwich.

（

GMT)

3. Date section (specifies the part of the date to return the new value.) The following table lists the date parts and their abbreviations that are recognized by Microsoft SQL Server 2008. ）

Date part

Meaning

Abbreviation year yy, yyyy

Quarter season QQ, Q

month mm, M

DayOfYear Day (see the description in the function) dy, y

Day days (see description in function) DD, d

Week week wk, WW

Weekday day (see the description in the function) DW, W

Hour hour HH

Minute min mi, n

Second S SS, s

Millisecond MS

4. Mathematical Functions

Function Name Parameter Example description

ABS (numeric expression)

Select ABS (-23.4) return 23.4

Returns the absolute value of the specified numeric expression (positive value)

Pi No parameters

Select PI () returns 3.14159265358979

Returns the value of π

cos (floating-point expression)

Select cos (PI ()/3) returns 0.5

Returns the cosine of the specified radian

Sin (floating-point expression)

Select Sin (pi ()/6) returns 0.5

Returns the sinusoidal value of the specified Radian

Cot (floating-point expression)

Select Cot (PI ()/4) return 1

Returns the cotangent value of the specified Radian

Tan (floating-point expression)

Select Tan (Pi ()/4) returns 1

Returns the tangent of the specified radian

ACOs (floating-point expression)

Select ACOs (0.5) returns 1.0471975511966

Returns the arc of the specified numeric expression for the remaining chord, and the inverse cosine

ASIN (floating-point expression)

Select ASIN (0.5) returns 0.523598775598299

Returns the arc whose sine is the specified numeric expression, seeking the inverse of the string

Atan (floating-point expression)

Select Atan (1) returns 0.785398163397448

Returns the arc whose tangent is the specified numeric expression, and the inverse tangent

Degrees

(Numeric expression)

Select degrees (Pi ()/4) returns 45

Returns the corresponding angle of the angle specified in radians.

radians (numeric expression)

Select radians (180.0)

Returns 3.1415926535897931

Returns the Radian value for the specified number of degrees. Note If you pass in an integer value, the returned result is

Will omit the decimal part

exp (floating-point expression)

Select exp (4) returns 54.5981500331442

Return to seek

The specified power of E, e=2.718281 ...

Log (floating-point expression)

Select log (6) returns 1.79175946922805

Returns the logarithm with the base e, which is the natural logarithm.

LOG10 (floating-point expression)

Select LOG10 (100) returns 2

Returns the base 10 logarithm

Ceiling (numeric expression)

Select Ceiling (5.44) returns 6

Select Ceiling (-8.44) return-8

Returns the smallest integer greater than or equal to the specified numeric expression.

Floor (numeric expression)

Select Floor (5.44) return 5

Select Floor (-8.44) return-9

Returns the largest integer less than or equal to the specified numeric expression.

Power (numeric expression 1, numeric expression 2)

Select Power (5,2) returns 25

Returns a numeric expression

1 Numeric Expression 2 power

sqrt (numeric expression)

Select sqrt (25) returns 5

Returns the square root of a numeric expression

sign (numeric expression)

Select SIGN (6) returns 1

Select sign (-6) return-1

Select sign (0) returns 0

Expression is positive return +1

Expression is negative return-1

Expression Zero returns 0

rand ([integer expression])

Select rand (100) returns 0.715436657367485

Select rand () returns 0.28463380767982

Select rand () returns 0.0131039082850364

Returns a random float value from 0 to 1. The integer expression is seed, and the same seed is used to produce the same random number. Even repeated calls to Rand () with the same seed value return the same result. If you do not specify a seed, the system randomly generates the seed.

Round (numeric expression [, length [, Mode of Operation]])

Select round (1236.555,2) returns 1236.560

Select round (1236.555,2,1) returns 1236.550

Select round (1236.555,0) returns 1237.000

Select round (1236.555,-1) returns 1240.000

Select round (1236.555,-1,1) returns 1230.000

Select round (1236.555,-2) returns 1200.000

Select round (1236.555,-3) returns 1000.000

Select round (1236.555,-4) returns 0.000

Error occurred in select Round (5236.555,-4)

Select round (5236.555,-4,1) returns 0.000

Returns a numeric value that is rounded to the specified length. Note the returned value and the original value

The total number of digits has not changed.

Length: Rounding Precision. If the length is positive, the value is rounded to the number of decimal digits specified in length. If the length is negative, rounds the left portion of the numeric decimal point to the length specified by length. Note If the length is negative and is greater than the number of digits before the decimal point, 0 is returned. If the length is negative and equal to the number of digits before the decimal point and the operation is rounded, the first one less than 5 returns 0, greater than or equal to 5 causes an error to occur, and if the action method is not rounded, no error occurs, and the returned result is invariably 0.

How to do this: The default is 0 to follow rounding, and the other integer values are truncated directly.

5. Data type Conversion functions

(The following two functions are similar, but convert also provides a rich style for date conversion, cast can only perform normal date conversions)

Function Name Parameter Example description

Convert (data type [(length)], expression [, style])

Select CONVERT (nvarchar,123) returns 123

Select N ' Age: ' +convert (nvarchar,23)

Return Age: 23 (Note: If you want the results to appear correctly in the

The text needs to precede the given string with N, plus n for the database to recognize Unicode characters)

Select convert (nvarchar, getdate ())

An expression that explicitly converts an expression of one data type to another data type. Length: If the data type allows you to set the length, you can set the length, such as the varchar (10) style: The style used to convert date type data to date formats for character data types. See the table below

Back to 10:21pm

Select convert (nvarchar, GETDATE (), 101)

Back to 04/28/2009

Select convert (nvarchar, getdate (), 120)

return 2009-04-28 12:22:21

Select convert (nvarchar), GETDATE (), 120)

Back to 2009-04-28

Cast (expression as data type [(length)])

Select CAST (123 as nvarchar) returns 123

Select N ' Age: ' +cast (as nvarchar)

Return Age: 23

An expression that explicitly converts an expression of one data type to another data type. A partial style sheet that converts date type data to a date format of a character data type when an expression of two different data types is combined with an operator, the data type precedence rule specifies that the lower priority data type is first converted to a higher priority type. If this conversion is not an implicit conversion that is supported, an error is returned. When two operand expressions have the same data type, the result of the operation is that data type. If you need to convert a high-priority data type to a low-priority data type, you need to use a data type conversion function for the display conversion.

SQL Server 2005 uses the following priority order for data types (first high, then low):

Without century digits (yy)

With century digits (yyyy)

Standard

Input/output-

0 or 100

Default settings Mon dd yyyy hh:miam (or PM) 1 101

United States

MM/DD/YYYY 2 102 ANSI yy.mm.dd 3103

UK/France

Dd/mm/yy 4 104

Germany

Dd.mm.yy 5 105

Italy

Dd-mm-yy 120

ODBC specification

Yyyy-mm-ddhh:mi:ss (24h)

1. user-defined data type (highest) 2,sql_variant 3,XML 4,datetime 5,smalldatetime 6, Float 7,real 8, decimal 9, money 10,smallmoney 11, bigint 12,int 13,Small int 14,tinyint 15,bit 16,ntext 17,text 18,image 19, timestamp 20, uniqueidentifier

21, nvarchar (including nvarchar (max)) 22,nchar 23,varchar (including varchar (max)) 24,Char 25,varbinary (including varbinary (max)) 26,binary (lowest)

System functions

Function Name Parameter Example description

NEWID no parameters

Select NEWID ()

Back to 2e6861ef-f4db-4ffe-85ea-638242f2e5f2

Select NEWID ()

Back to 09bbde6f-47c2-4e2e-81e8-afc50592280c

Returns a GUID (globally unique identifier) value

IsNumeric (arbitrary expression)

Select IsNumeric (1111) returns 1

Select IsNumeric (' 123RR ') returns 0

Select IsNumeric (' 123 ') returns 1

Determines whether an expression is a numeric type or can be converted to a numeric value. is return 1, not return 0

IsNull (arbitrary expression 1, Arbitrary expression 2)

Select IsNull (null,n ' no value ') returns no value

Select IsNull (n ' specific value ', n ' no value ')

Returns the specific value

If an arbitrary expression

1 is not NULL, its value is returned, otherwise, any expression

The type of 2 is converted to the type of any expression 1 (if the two types are different), returning any table

A value of 2.

IsDate (arbitrary expression)

Select IsDate (GETDATE ()) returns 1

Select IsDate (' 1988-1-1 ') returns 1

Select IsDate (' 198 ') returns 0

Determines whether the input expression is a valid date or can be turned into a valid date. is return 1, not return 0

Ranking function

Common use format for ranking functions: function name () over (Order by column name [asc|desc][, column name ...])

Note: The return result set is sorted according to the column specified when the Rank function is used, so do not use ORDER by again after the FROM clause, resulting in a confusing ranking.

Function name

Parameter Example Description

Row_number No parameters

Select Roductid,[name],listprice, Row_number ()

Over (order by ListPrice Desc) as rank from production.product

Number each row in the result set, starting with 1, followed by 1.

Rank no parameters

Select Productid,[name],listprice,rank ()

Over (order by ListPrice Desc) as Rank

From Production.Product

If two or more lines are associated with a rank, each association line gets the same rank, and the rank is not contiguous. For example, if the prices of the two products with the highest prices are the same, they will be listed first. Since there are already two lines ranked in front, the product with the next highest price will be ranked third. The rank equals the number of rows before the row plus one. So

The RANK function does not always return consecutive integers.

Dense_rank

No parameters

Select

Productid,[name],listprice,dense_rank ()

Over (order by ListPrice Desc) as Rank

From Production.Product

If two or more lines are associated with a rank, each association row gets the same rank, ranked consecutively.

For example, if the prices of the two products with the highest prices are the same, they will be tie, the next highest price

The product will be ranked second. So

The number returned by the Dense_rank function is uninterrupted and begins

The end has a continuous ranking.

Aggregation functions

Aggregate functions perform calculations on a set of values

, and returns a single value. In addition to COUNT, the aggregate function ignores null values. Aggregate functions are often associated with the GROUP by child of the SELECT statement

Sentence to be used together. Common use format for aggregate functions: function name ([all|distinct] expression)

All: The default value, the aggregate function operation for all values contains duplicate values. Distinct: Eliminate

Aggregate function operations are performed with the exception of duplicate values.

Function Name Example Description

Avg

SELECT avg (vacationhours) as ' average vacation hours '

From HumanResources.Employee

WHERE Title like ' Vice president% '

Returns 25

Returns the average of the values in the group. Null values will be ignored. An expression is a numeric expression.

Count

SELECT Count (*) from Production.Product returns 504

SELECT count (Color) from Production.Product returns 256

SELECT count (distinct Color) from Production.Product

Returns 9

Returns the number of items in a group. COUNT (*) returns the number of items in the group. Includes NULL values and duplicates. If you specify

An expression ignores null values. An expression is an arbitrary expression.

Min

Select min (ListPrice) from Production.Product returns 0

Returns the minimum value in a group. Null values will be ignored. An expression is a numeric expression, a string expression, and a date.

Max

Select Max (ListPrice) from Production.Product

Returns 3578.27

Returns the maximum value in a group. Null values will be ignored. An expression is a numeric expression, a string expression, and a date.

Sum

SELECT sum (sickleavehours) as ' total sick hour '

From HumanResources.Employee

WHERE Title like ' Vice president% '; Returns 97

Returns the and of all values in the group. Null values will be ignored. Expression is a numeric expression

Translated from: http://blog.csdn.net/travylee/article/details/7216481

SQL Server Functions Daquan