MSSQL Server Functions

One, character conversion function
1. ASCII ()
Returns the ASCII value of the leftmost character of a character expression. In the ASCII () function, a string of pure numbers is not "enclosed", but a string with other characters must be used in ' surround ', or an error will occur.
2, CHAR ()
Converts an ASCII code to a character. If you do not enter an ASCII value between 0 and 255, CHAR () returns NULL.
3, LOWER () and Upper ()
LOWER () converts all strings to lowercase, UPPER () capitalizes all strings.
4. STR ()
Converts numeric data to character data.
STR (<float_expression>[,length[, <decimal>])
length specifies the size of the returned string, and decimal specifies the number of decimal digits to return. If the length is not specified, the default value is ten, and the decimal default is 0.
Returns null when length or decimal is a negative value;
When length is less than the number of digits to the left of the decimal point (including the sign bit), the length of * is returned;
Obey length first, then take decimal;
When the number of string bits returned is less than length, the left side complements the space.
second, go to the space function
1. LTRIM () Remove the space at the head of the string.
2. RTRIM () Remove the space at the trailing end of the string.
Three, take the SUBSTRING function
1. Left ()
Left (&LT;CHARACTER_EXPRESSION&GT;, <integer_expression>)
Returns the integer_expression character from the left of character_expression.
2. Right ()
Right (&LT;CHARACTER_EXPRESSION&GT;, <integer_expression>)
Returns the integer_expression characters from the right of character_expression.
3, SUBSTRING ()
SUBSTRING (<expression>, <starting_ position>, length)
Returns the section Starting_ position characters character length from the left of a string.
Four, string comparison function
1, CHARINDEX ()
Returns the beginning of the occurrence of a specified substring in a string.
CHARINDEX (< ' substring_expression ';, <expression>)
Where substring _expression is the character expression to look for, expression can be a string or a column name. If no substring is found, a value of 0 is returned.
This function cannot be used with text and image data types.
2, PATINDEX ()
Returns the beginning of the occurrence of a specified substring in a string.
PATINDEX (< '%substring _expression% ';, <column_ name>) where the substring expression must have a percent semicolon "%" or the return value is 0.
Unlike the CHARINDEX function, wildcard characters can be used in substrings of the PATINDEX function, and this function can be used with char, VARCHAR, and text data types.
Five, string manipulation functions
1, QUOTENAME ()
Returns a string that is enclosed by a specific character.
QUOTENAME (< ' character_expression ' >[, quote_ character]) where Quote_ character the character used to enclose the string, the default value is "[]".
2, REPLICATE ()
Returns a string that repeats character_expression the specified number of times.
REPLICATE (character_expression integer_expression) returns null if the Integer_expression value is negative.
3, REVERSE ()
Reverses the character order of the specified string.
REVERSE (<character_expression>) where character_expression can be a string, a constant, or a column's value.
4. REPLACE ()
Returns the string that was replaced by the specified substring.
REPLACE (&LT;STRING_EXPRESSION1&GT;, <string_expression2>, <string_expression3>) with String_expression3 Replace the substring string_expression2 in the string_expression1.
4. SPACE ()
Returns a blank string with the specified length.
SPACE (<integer_expression>) returns null if the Integer_expression value is negative.
5, STUFF ()
Specifies a substring of the position, length, with another substring substitution string.
STUFF (<character_expression1>, <start_ Position>, <length>,<character_expression2>)
A null value is returned if the starting position is negative or the length value is negative, or the starting position is greater than the length of character_expression1.
If length is greater than start_ position in Character_expression1 to the right length, Character_expression1 retains only the first character.
VI. data type conversion Functions
1. CAST ()
CAST (<expression> as <data_ type>[length])
2. CONVERT ()
CONVERT (<data_ type>[length], <expression> [, Style])
1) data_type The data type defined for the SQL Server system, the user-defined data type cannot be used here.
2) length is used to specify how long the data is, and the default value is 30.
3) converts a char or varchar type to an integer type such as int or samllint, and the result must be a numeric value with a plus or minus sign.
4) The text type is converted to char or varchar type up to 8,000 characters, that is, the char or varchar data type is the maximum length.
5) The data stored by the image type is converted to binary or varbinary type, up to a maximum of 8,000 characters.
6) Convert integer value to Money or smallmoney type, processed in the currency unit of the defined country, such as RMB, USD, GBP, etc.
7) A bit type conversion converts a non-0 value to 1 and is still stored as a bit type.
8) attempts to convert to a different length of data type will truncate the conversion value and display "+" after the value is converted to identify the truncation occurred.
9) Use the style option of the CONVERT () function to display the date and time in a different format. The style is the conversion style number that is provided by the SQL Server system when converting Datatime and smalldatetime data to strings, and different style numbers have different output formats.
Seven, date function
1. Day (date_expression)
Returns the date value in Date_expression
2. Month (date_expression)
Returns the month value in Date_expression
3. Year (date_expression)
Returns the year value in Date_expression
4, DATEADD ()
DATEADD (&LT;DATEPART&GT;, <number>, <date>)
Returns the new date specified by date plus the specified extra date interval number.
5, DATEDIFF ()
DATEDIFF (&LT;DATEPART&GT;, <date1>, <date2>)
Returns the DATEPART difference between the two specified dates, that is, the date2 exceeds the value of date1, and the resulting value is an integer value with a positive sign.
6, Datename ()
Datename (&LT;DATEPART&GT;, <date>)
Returns the specified part of the date as a string in this section. specified by DatePart.
7, DATEPART ()
DATEPART (&LT;DATEPART&GT;, <date>)
Returns the specified part of the date as an integer value. This section is specified by DatePart.
DATEPART (dd, date) is equivalent to day (date)
DATEPART (mm, date) equals month (date)
DATEPART (yy, date) is equivalent to year (date)
8, GETDATE ()
Returns the current date and time of the system in the default format of DateTime.

Focus

Date and time functions in SQL Server? Ponki (sleeping?)
1. Current system date, timeF. Sham Q?
Select GETDATE ()Gathering h merger built
鶞 Oh Shepherd/"Kwai
2. DateAdd returns a new datetime value based on adding a period of time to the specified dateR Saurida Qiao??
Example: Add 2 days to the date? 5 chapped? 煃
Select DATEADD (day,2, ' 2004-10-15 ')--return: 2004-10-17 00:00:00.000EW 郬 * 矝?
礛 Pozdyshev;??
3. DateDiff returns the number of date and time boundaries across two specified dates.Jou sterilized sapping
Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 17? 鼄 G 驂 W
毰 Tube???
4. DatePart returns an integer representing the specified date portion of the specified date.劸 compensate d///穙
SELECT DATEPART (month, ' 2004-10-15 ')--return 10RK 銍 almost k$7$
Minari Jae u!:?
5. Datename returns a string representing the specified date part of the specified date堼 Kaji u throat
SELECT Datename (Weekday, ' 2004-10-15 ')--return: Fridays 吜 Blind D 湺
' 鈠 6.
6. Day (), month (), year ()--can be compared with datepartV?u Board/M
? Hz? S
Select Current date =convert (varchar), GETDATE (), 120)S1 喨??
, Current time =convert (varchar (8), GETDATE (), 114)i.e. N 屾 蛶 4
Anti-Is栲栳栳 b caesium k '/
Select Datename (DW, ' 2004-10-15 ')? 3 tear 萳 籋?
b?|
Select how many weeks of the year =datename (week, ' 2004-10-15 ')宎 Ladder d< short?
, today is a few weeks =datename (weekday, ' 2004-10-15 ')Fight? 1 Clenbuterol Gschirnwirt
Ohrid 聈 W]?n
Yan <8 surname 婨?
function parameters/FunctionsY? mo??
GetDate () returns the current date and time of the system' 瞫 Discard # 縶?
DateDiff (INTERVAL,DATE1,DATE2) returns the difference between Date2 and date1 two dates in the interval specified date2-date1Grebe Information 儔 chop?
DATEADD (interval,number,date) with the date specified in interval, plus number"€ Bell e flies 5 stream
DatePart (interval,date) returns the integer value corresponding to the specified portion of the date, intervalDraw/蟉 wrong
Datename (interval,date) returns the name of the string that corresponds to the specified part of the date, interval杶 Lu were threat c
Raving h:l=?
The setting values for the parameter interval are as follows:掅 yw@ Crucian carp =
8 ho?
Description of the value abbreviation (SQL Server) (Access and ASP)Nest T) 軤
Year Yy yyyy 1753 ~ 9999A GA ringworm 0QG
Quarter Qq Q Season 1 ~ 4駨 # primitively 沠? F
Month Mm M 1 ~ 12O?c (A Yiu 蒪
Day of the year Dy y the number of days of the year, the day of the first 1-366Y 觲 Kamimachi 褽
Day Dd D, 1-31Yue 綪 嶴 Exaggeration
Weekday Dw W Day of the week, day of the week 1-7Sock 齀 &?
Week Wk WW Week, week of the year 0 ~ 51? X 葤 7 膧
Hour Hh H 0 ~ 23F? F minor u 暜?
Minute Mi N min 0 ~ 59Jung "~r Swerve O
Second Ss s seconds 0 ~ 59? 6Y? "; D
Millisecond MS-MS 0 ~ 999AQ color 堐? E8
? flash spilling 麘 ke
Ъ@p t3€
In Access and ASP, date () and now () are used to obtain system datetime, where Datediff,dateadd,datepart is also used in Access and ASP, and the usage of these functions is similarAccountant? b '?
Hlu Akaiya P Bingo
Example:/0R the symbol of helium?
1.GetDate () for SQL Server:select GetDate ()Like 4 sides?
X "g#$
2.DateDiff (' s ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 514,592 secondso Mozart ⒚
DateDiff (' d ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 5 days Luo kic sufficient 嬟
v€ terrain Rokkei? 觬
3.DatePart (' W ', ' 2005-7-25 22:56:32 ') returns a value of 2 that is Monday (Sunday is 1, Saturday is 7) ? 聕 qV0?
DatePart (' d ', ' 2005-7-25 22:56:32 ') The return value is 25 that is 25th number y Xuan 1 yong break?
DatePart (' y ', ' 2005-7-25 22:56:32 ') returns a value of 206 that is the No. 206 day of the year marrow?? 驐?
DatePart (' yyyy ', ' 2005-7-25 22:56:32 ') returns a value of 2005 that is 2005 ?

Internal aggregate functions
1) Count (*) returns the number of rows
2) Count (DISTINCT colname) returns the number of unique values in the specified column
3) SUM (colname/expression) Returns the value of the specified column or expression;
4) SUM (DISTINCT colname) returns the number of unique values in the specified column and
5) AVG (colname/expression) returns the numeric mean in the specified column or expression
6) AVG (DISTINCT colname) returns the average of the unique values in the specified column
7) min (colname/expression) returns the minimum value of a value in a specified column or expression
8) Max (colname/expression) returns the numeric maximum value in the specified column or expression

System functions

Suser_name () User login name
USER_NAME () The name of the user in the database
User's name in the database
Show_role () rules that work for the current user
Db_name () database name
object_name (obj_id) database object name
Col_name (obj_id,col_id) column name
Col_length (objname,colname) column length
Valid_name (char_expr) is a valid identifier

MSSQL Server Functions