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
sqlserver2008 function 1