Common functions for SQL Server small sinks

Extracts some of the functions commonly used by SQL Server to facilitate daily learning

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.

VIII. statistical functions

AVG ()-return average value
Count ()-Number of rows returned
First ()-Returns the number one value
Last ()-Returns the final value
Max ()-The maximum value returned
MIN ()-Returns the minimum value
Total ()-The sum returned

nine, mathematical functions
ABS (NUMERIC_EXPR) Seek absolute value
Ceiling (numeric_expr) takes the smallest integer greater than or equal to the specified value
EXP (float_expr) Fetch index
Floor (numeric_expr) is less than or equal to the specified worth maximum integer
Pi () 3.1415926 .....
Power (Numeric_expr,power) back to power sub-party
RAND ([int_expr]) random number generator
Round (numeric_expr,int_expr) Ann int_expr Specifies the accuracy of rounding
Sign (INT_EXPR) based on positive, 0, negative, return +1,0,-1
sqrt (float_expr) square root

10. 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

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 field1) as row_number,* from t_table

The SQL statement above is shown in query result 2.

Figure 2

Where the Row_number column is the ordinal column generated by the Row_number function. The use of the Row_number function is to use the over clause to choose to sort a column before the ordinal can be generated.

In fact, the basic principle of generating ordinal numbers with the Row_number function is to sort the records by using the sort statements in the over clause, and then generate the ordinal numbers in that order. The ORDER BY clause in the OVER clause has nothing to do with the ORDER BY clause in the SQL statement, and the order by in these two places is completely different, as 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 the 2nd and 3rd records in the T_table table:

With t_rowtable
As
(
Select Row_number () over (order by field1) as row_number,* from t_table
)
SELECT * from t_rowtable where row_number>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 to 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:

SELECT * FROM (SELECT top 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 Rank () over (order by field1), * from T_table ORDER by field1

Figure 7

The SQL statement above is shown in query result 7.

Third, Dense_rank

The function of the Dense_rank function is similar to the rank function, except that it is sequential when the ordinal is generated, and the rank function may not produce sequential numbers. As in the above example, if you use the Dense_rank function, the 4th record should be numbered 2 instead of 4. As shown in the following SQL statement:

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:

Select Ntile (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:

MoD indicates the remainder, Div indicates rounding
If (total number of records mod barrels = = 0)
{
RecordCount = total number of records div buckets;
Set the number of records per bucket to RecordCount
}
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
}

Common functions for SQL Server small sinks