Summary of SQL Common 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.

5, SPACE ()
Returns a blank string with the specified length.
SPACE (<integer_expression>) returns null if the Integer_expression value is negative.

6, 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.

9. A very powerful date formatting function in SQL Server
Select CONVERT (varchar), GETDATE (), 0): 2006 10:57am
Select CONVERT (varchar), GETDATE (), 1): 05/16/06
Select CONVERT (varchar), GETDATE (), 2): 06.05.16
Select CONVERT (varchar), GETDATE (), 3): 16/05/06
Select CONVERT (varchar), GETDATE (), 4): 16.05.06
Select CONVERT (varchar), GETDATE (), 5): 16-05-06
Select CONVERT (varchar), GETDATE (), 6): 16 05 06
Select CONVERT (varchar), GETDATE (), 7): 05 16, 06
Select CONVERT (varchar), GETDATE (), 8): 10:57:46
Select CONVERT (varchar), GETDATE (), 9): 2006 10:57:46:827am
Select CONVERT (varchar), GETDATE (), 10): 05-16-06
Select CONVERT (varchar), GETDATE (), 11): 06/05/16
Select CONVERT (varchar), GETDATE (), 12): 060516
Select CONVERT (varchar), GETDATE (), 13): 16 05 2006 10:57:46:937
Select CONVERT (varchar), GETDATE (), 14): 10:57:46:967
Select CONVERT (varchar), GETDATE (), 20): 2006-05-16 10:57:47
Select CONVERT (varchar), GETDATE (), 21): 2006-05-16 10:57:47.157
Select CONVERT (varchar), GETDATE (): 05/16/06 10:57:47 AM
Select CONVERT (varchar), GETDATE (), 23): 2006-05-16
Select CONVERT (varchar), GETDATE (), 24): 10:57:47
Select CONVERT (varchar), GETDATE (), 25): 2006-05-16 10:57:47.250
Select CONVERT (varchar), GETDATE (), (+): 2006 10:57am
Select CONVERT (varchar), GETDATE (), 101): 05/16/2006
Select CONVERT (varchar), GETDATE (), 102): 2006.05.16
Select CONVERT (varchar), GETDATE (), 103): 16/05/2006
Select CONVERT (varchar), GETDATE (), 104): 16.05.2006
Select CONVERT (varchar), GETDATE (), 105): 16-05-2006
Select CONVERT (varchar), GETDATE (), 106): 16 05 2006
Select CONVERT (varchar), GETDATE (), 107): 05 16, 2006
Select CONVERT (varchar), GETDATE (), 108): 10:57:49
Select CONVERT (varchar), GETDATE (), 109): 2006 10:57:49:437am
Select CONVERT (varchar), GETDATE (), 110): 05-16-2006
Select CONVERT (varchar), GETDATE (), 111): 2006/05/16
Select CONVERT (varchar), GETDATE (), 112): 20060516
Select CONVERT (varchar), GETDATE (), 113): 16 05 2006 10:57:49:513
Select CONVERT (varchar), GETDATE (), 114): 10:57:49:547
Select CONVERT (varchar), GETDATE (), 120): 2006-05-16 10:57:49
Select CONVERT (varchar), GETDATE (), 121): 2006-05-16 10:57:49.700
Select CONVERT (varchar), GETDATE (), 126): 2006-05-16t10:57:49.827
Select CONVERT (varchar), GETDATE (), ():???? ?????? 1427 10:57:49:907am
Select CONVERT (varchar), GETDATE (), 131): 18/04/1427 10:57:49:920am

Common:
Select CONVERT (varchar), GETDATE (), 8): 10:57:46
Select CONVERT (varchar), GETDATE (), 24): 10:57:47
Select CONVERT (varchar), GETDATE (), 108): 10:57:49
Select CONVERT (varchar), GETDATE (), 12): 060516
Select CONVERT (varchar), GETDATE (), 23): 2006-05-16

Viii. Statistical Functions

Avg ()-The average value returned
count ()-The number of rows returned
first ()-Returns the value of
last ()-Returns the final value
Max ()-Returns the maximum value
min ()-Returns the minimum value
Total ()-returned sum

ix, math function
ABS (numeric_expr) for absolute value
Ceiling (numeric_expr) Take the smallest integer greater than or equal to the specified value
exp (float_expr) index
Floor (numeric_expr) is less than or equal to the specified worth the largest integer
Pi () 3.1415926 ...
Power (Numeric_expr,power) returns the power sub-party
Rand ([int_expr]) random number generator
Round (numeric_expr,int_expr) an int_expr-defined precision rounding
Sign (INT_EXPR) based on positive number, 0, negative number, return +1,0,-1
sqrt (float_expr) square root

10, system function
suser_name () User logon name
User_name () the user's name in the database
the user's first name in the database
Show_role () rules that affect 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

11, partial instances of functions
1:replace functions
First parameter your string, second parameter you want to replace the part, the third parameter you want to replace with what
Select Replace (' Lihan ', ' A ', ' B ')

-----------------------------
Lihbn

(The number of rows affected is 1 rows)
=========================================================
2:substring function
The first parameter is your string, the second one is the beginning of the replacement position, the third end replaces the position
Select substring (' Lihan ', 0, 3);
-----
Li

(The number of rows affected is 1 rows)
=========================================================
3:charindex function
The first parameter you are looking for char, the second parameter you are looking for the string return parameter one in the position of parameter two
Select CHARINDEX (' A ', ' Lihan ')
-----------
4

(The number of rows affected is 1 rows)

===========================================================
4:ascii function
Returns the ASCII code value of the leftmost character in a character expression.
Select ASCII (' Lihan ')
-----------
108

(The number of rows affected is 1 rows)

================================================================
5:nchar function
Returns the Unicode character with the specified integer code, as defined by the Unicode standard.
The parameter is a positive integer between 0 and 65535. If a value beyond this range is specified, NULL is returned.
Select NCHAR (3213)
----
Unicode characters

(The number of rows affected is 1 rows)

=========================================================
6:soundex
Returns a four-character code (SOUNDEX) that evaluates the similarity of two strings.
SELECT SOUNDEX (' Lihan '), SOUNDEX (' Lihon ');
----- -----
L546 L542

(The number of rows affected is 1 rows)
=========================================================
7:char
The parameter is an integer between 0 and 255. If the integer expression is not within this range, a NULL value is returned.
SELECT Char (125)
----
}

(The number of rows affected is 1 rows)

==========================================================
8:str function
The first argument must be a number, the second parameter represents the position converted to char, less than the parameter one position *, greater than the right alignment
SELECT Str (12345,3)
----
***

(The number of rows affected is 1 rows)

SELECT Str (12345,12)
------------
12345

(The number of rows affected is 1 rows)
===========================================================
9:difference function
Returns an integer value that indicates the difference between the SOUNDEX values of a two-character expression.
The returned integer is the number of identical characters in the SOUNDEX value. The returned values range from 0 to 4:0 means almost different or completely different, and 4 means almost the same or exactly the same.
SELECT difference (' Lihan ', ' Liha ')
-----------
3

(The number of rows affected is 1 rows)

==================================================================
10:stuff function (four parameters)
function to insert a string into another string. It removes the specified length of characters from the starting position in the first string, and then inserts the second string at the beginning of the first string.
SELECT stuff (' Lihan ', 2,3, ' Lihan ')
--------
Llihann

(The number of rows affected is 1 rows)
===============================================================

11:left function
Returns the leftmost n characters, determined by the parameter
Select Left (' Lihan ', 4)
-----
Liha

(The number of rows affected is 1 rows)
================================================================

The Right function
Returns the rightmost n characters, determined by the parameter
Select Right (' Lihan ', 4)
-----
Ihan

(The number of rows affected is 1 rows)
================================================================

13:replicate function
I think it's a parameter. Copy parameter two times
Select Replicate (' Lihan ', 4)
--------------------
Lihanlihanlihanlihan

(The number of rows affected is 1 rows)

================================================================

14:len function
Return parameter length
Select Len (' Lihan ')
-----------
5

(The number of rows affected is 1 rows)

================================================================
15:reverse function
Invert string
Select reverse (' Lihan ')
-----
Nahil

(The number of rows affected is 1 rows)

=================================================================

16:lower and Upper functions
Parameter Case Conversion
Select Lower (Upper (' Lihan '))
--------------------
Lihan

(The number of rows affected is 1 rows)

====================================================================

17:ltrim and RTrim functions
Remove left and right spaces
Select LTrim (' Lihan ')
--------------------------
Lihan

(The number of rows affected is 1 rows)
Select RTrim (' Lihan ')
---------
Lihan

(The number of rows affected is 1 rows)

Additional:
The ranking function is a new addition to the SQL Server2005 feature. There are four ranking functions in SQL Server2005:
1. Row_number
2. Rank
3. Dense_rank
4. Ntile
The following describes the functions and usage of these four ranking functions respectively. Before the introduction, assume that there is a t_table table with table structure as shown in table Data 1:

Figure 1

Where the type of the Field1 field is the type of the Int,field2 field is varchar

First, Row_number

The purpose of the Row_number function is very extensive, and the function is to generate a sequence number for each row of records that are queried. The use of the Row_number function is shown in the following SQL statement:

Select Row_number () over (order by field2 Desc) as row_number,* from t_table ORDER BY field1 Desc

The SQL statement above is shown in query result 3.

Figure 3 We can use the Row_number function to implement a record of the specified range in the query table, which is typically applied to the paging functionality of the Web application. The following SQL statement can query 2nd and 3rd records in the T_table table: 1 and Row_number < 4 ORDER by field1

The SQL statement above is shown in query result 4.

Figure 4 The above SQL statement uses the CTE, and the introduction to the CTE will read about SQL Server2005 (1): Simplify nested SQL with common table expressions (CTE).
It is also important to note that if you use the Row_number function for paging, the order by in the over clause should be the same as the order by of the sorted record, otherwise the resulting sequence number may not be continuous.
Of course, not using the Row_number function can also be implemented to query the specified range of records, is more troublesome. The general method is to use upside down to achieve, for example, query t_table table in the 2nd and 3rd records, you can find the first 3 records, and then the query out of these three records in reverse order, then take the first 2 records, and finally the check out the 2 records and then sorted in reverse order, is the final result. The SQL statements are as follows: 2 * FROM (SELECT Top 3 * from T_table ORDER by field1) a order by field1 Desc) b ORDER by field1

Figure 5
The above SQL statement is queried for the results shown in 5.

The results of this query are identical to the query results shown in Figure 4 except that there is no ordinal column row_number.

Second, rank

The rank function takes into account the same value of the sort field in the over clause, and in order to make it easier to explain the problem, add a record in the T_table table, as shown in 6.

Figure 6
The Field1 field values for the following three records are the same in the records shown in Figure 6. If you use the Rank function to generate the ordinal number, the 3 records are the same sequence number, and the 4th record generates a sequence number based on the current number of records, followed by the record, that is, in this case, the 4th record ordinal is 4, not 2. The rank function is used in exactly the same way as the Row_number function, and the SQL statement is as follows:

Select Dense_rank () over (order by field1), * from T_table ORDER by field1

The SQL statement above is shown in query result 8.

Figure 8

Readers can compare the query results shown in Figure 7 and Figure 8

Iv. Ntile
The Ntile function can be used to group ordinal numbers. This is equivalent to putting the queried recordset into an array of a specified length, with each array element holding a certain number of records. The ordinal number generated by the Ntile function for each record is the index of all the array elements in this record (starting at 1). You can also refer to each array element of an allocation record as a "bucket." The Ntile function has an argument to specify the number of buckets. The following SQL statement uses the Ntile function to drum up the t_table table:
4) Over (order by field1) as bucket,* from t_table

Figure 9

The SQL statement above is shown in query result 9.

Because the total number of records for the t_table table is 6, the Ntile function in the SQL statement above specifies that the number of buckets is 4.

Perhaps some readers will ask such a question, how does SQL Server2005 decide how many records a bucket should put? Perhaps the number of records in the T_table table is somewhat small, so we assume that there are 59 records in the T_table table, and the number of buckets is 5, so how many records should be put in each bucket?

In fact, with two conventions, you can produce an algorithm that determines how many records should be placed on which bucket, and the two conventions are as follows:

1. The record of a small bucket can not be smaller than the number of buckets. That is, the number of records in the 1th stab can be greater than or equal to the record in the 2nd barrel and later barrels.

2. All the records in the bucket are either the same, or the number of records in the bucket after the beginning of a small record is the same as the number of records in that bucket. That is, if there is a bucket, the first three barrels of record count is 10, and the number of the 4th Poke is 6, then the 5th and 6th barrels of records must also be 6.

According to the above two conventions, the following algorithm can be obtained:

Else
{
RecordCount1 = total number of records div barrels + 1;
int n = 1; n represents the maximum number of buckets in a bucket with a record count of RecordCount1
m = recordCount1 * N;
while ((Total Records-m) mod (number of barrels-N))! = 0)
{
n++;
m = recordCount1 * N;
}
RecordCount2 = (Total Records-m) div (number of buckets-N);
Set the number of records for the first n buckets to RecordCount1
Set the number of records for n + 1 to all buckets to RecordCount2
}

Based on the algorithm above, if the total number of records is 59 and the number of barrels is 5, the first 4 buckets are 12, and the last bucket has a record count of 11.

If the total number of records is 53 and the number of barrels is 5, the first 3 buckets have a record count of 11, and the last 2 buckets have a record count of 10.

Taking this example, the total number of records is 6, the number of barrels is 4, then the value of RECORDCOUNT1 is calculated to be 2, after the end of the while loop, the value of RecordCount2 is 1, so the first 2 buckets of the record is 2, the last 2 barrels of the record is 1.

Summary of SQL Common functions