Summary of practical functions of T-SQL

T-SQL is the implementation of Microsoft SQL-3 standard in the relational database management system SQL Server, is the expansion of SQL, has the main characteristics of SQL, at the same time, language elements such as variables, operators, functions, process control, and annotations are added to make them more powerful. And the T-SQL comes with a lot of practical functions, DBA can use these functions for more effective management.

T-SQL string functions

T-SQL string function-truncates the value of a string

The following functions detail how to intercept a part of a string. Most of these functions are included in the following SELECT statement:

Select Productname,
Left (Productname, 12 ) As A,
Substring (Productname, 9 , 4 ) As B,
Right (Productname, 2 ) As C,
Rtrim (Productname) As D
From Products

The SELECT statement contains the left, substring, right, and rtrim functions. Execution result:

The left function returns the first 12 characters of the productname column. This function has two parameters: the first parameter is a string expression, and the second parameter is the number of characters. In this example, the string expression is the productname column and the number of characters is "12 ". As a result, the second half of all values in the productname column is truncated, and only 12 characters are left to return.

The use of the Right function is the same. It truncates the right part of the string.

The substring function can extract any part of the string. This function has three parameters: the first parameter is a string expression, the second parameter is to specify where to start the truncation, and the third parameter indicates the length of the string to be truncated. In the preceding example, the string expression is still the productname column. The second parameter "9" means that we should start from the ninth character (truncation), and the third parameter "4" means to get four characters.

The rtrim function removes all spaces at the end of the string (specified in the first parameter. The rtrim function also has another function relative to it: ltrim function. It deletes spaces on the left of the string.

T-SQL string function --- modify the value of a string

The T-SQL also includes a set of functions that can modify string values in SQL Server. The following statement is an example:

Code

Select  
Replace (Productname, ' Mountain ' , ' MTN ' ) As Replacename,
Stuff (Productname, 11 , 2 , ' X01 ' ) As Stuffname,
Reverse (Productname) As Reversenum,
Stuff (Productname, 1 , 2 , Replicate ( 0 , 3 )) As Replicatenum,
Productname +   Space ( 4 ) +   Lower (Productname) As Lowerprodline
From Products

Select statements include the repalce, stuff, reverse, replicate, and lower functions. Execution result:

 

The replace function is used to replace a set of strings specified in a given string value with a new string. This function has three parameters: the first parameter is a string expression, the second parameter is the string to be replaced, and the third parameter is the new string to be replaced.

The function stuff is used to delete a group of specified characters at a given position and insert a group of new characters. This function has four parameters: the first parameter is the string expression to be operated, the second parameter is the starting position of the string to be deleted, and the third parameter is the number of characters to be deleted, the fourth parameter is the string to be inserted. In the preceding example, the stuff function specifies the productname column as the string to be operated. The string to be deleted starts from 11th characters (the second parameter) and ends with two characters (the third parameter ). After the characters are deleted, the new characters (x01) are inserted to this position.

The function reservse is used to sort the given strings in reverse order.

Function replicate, which can repeat the specified number of times. This function has two parameters: the first parameter is the string value to be processed, and the second parameter is the number of repetitions. In the preceding example, "0" is repeated three times.

The function space is similar to the replicate function. It returns a group of spaces. This function has a parameter that specifies the number of spaces returned.

The lower function converts uppercase characters in a string to lowercase characters.

T-SQL string function --- convert string value

The T-SQL provides a function that can convert string values, as shown in the following SELECT statement:

Select  
Productname +   Char ( 9 ) +   Str (Productid) As Product,
ASCII (Productname) asciiproductname,
Rtrim (Productname) As Productname,
Unicode ( Rtrim (Productname )) As Unicodeproductname From Products

The SELECT statement contains the char, STR, ASCII, rtrim, and Unicode functions. Execution result:

The first function is Char. You can convert an ascii integer (from 0 to 255) to a character. This function has only one parameter (an ASCII integer ). This function is very convenient when you insert control characters to your string, such as the tab key (ASCII Code 9) and line feed symbol (ASCII code 10) or carriage return (ASCII code 13 ).

The STR function converts numeric data to numeric data. The STR function parameter is the numeric expression to be converted.

The T-SQL also supports ASCII functions that can convert a single character to an ASCII integer.

Unicode function, which is used to convert a character to a unicode integer. This function has only one parameter: UNICODE character expression.

T-SQL string function-get information about a string

There are also some functions that provide information related to strings, such as the length of strings or the position of internal substrings of values. The following SELECT statement demonstrates several functions that provide string-related information:

Select   Len (Productname) As Lengthproductname,
Charindex ( ' Bob ' , Productname) As Charindexproductname,
Patindex ( ' % Pears % ' , Productname) As Patindexproductname
From Products Where Productid = 7

The SELECT statement contains the char, STR, ASCII, rtrim, and Unicode functions. Execution result:

 

The LEN function returns the number of characters of a specified string. Only the unique parameter of this function is the string to be counted.

The charindex function is used to find the position of a string from the starting position specified in a string. This function has three parameters: the first parameter is the string to be searched, the second parameter is the string to be searched for range, and the third parameter is the start position of the search. The last parameter is optional. In the above example, we search for Bob's first position in the productname column, which starts from the second position of the string.

The patindex function. This function searches for the position where a string appears for the first time in the string. This function has two parameters: the first parameter is the string to be matched, and the second parameter is the string to be searched. You can use wildcards to define matching modes and provide flexible search modes. However, if you use the wildcard "%", you must add it before or after the character to be searched, unless you search for matches that start or end with a specified string. For example, in the preceding statement, "% pears %" is specified as the matching mode to be searched in the productname value. The results show that the instance that is matched for the first time is 27th characters long.

T-SQL collection functions

T-SQL Aggregate functions-basic functions

When operating data in the SQL Server database, you may need to perform calculations on a group of values. This is a basic concept.

Max function: Maximum function. Min function: min function. AVG function: Average function. Sum function: sum function. Count function: total function.

When using a set function to process grouped data, you can use the rollup, cube, or gourping sets operator to specify additional statistics contained in the result set.

T-SQL collection function-get statistics

SQL Server has four collection functions that allow you to perform data statistics on a group of values in a numeric column:

STDev: statistical standard variance of returned values.
Stdevp: returns the statistical standard variance of the population value.
VaR: statistical variance of the return value.
Varp: returns the statistical variance of the population value.

Select   STDev (Unitprice) As Stdevprice,
Stdevp (Unitprice) As Stdevpprice,
VaR (Unitprice) As Varprice,
Varp (Unitprice) As Varpprice From Products

Execution result:

T-SQL system functions 

SQL Server has built-in functions that support you to execute various tasks, such as converting data, operating string values, and executing mathematical functions. Some SQL server functions are classified as system functions. These functions allow you to view information about update and insert operations, extract server attribute settings, and access the details of the current session and its users. You can also use system functions to execute many language-related tasks.

T-SQL system functions-view information about data modifications 

Some system functions of SQL server support getting detailed information about your update and insert operations. For example, you can obtain the last id value inserted to a table, or add or update the number of rows to a table. To demonstrate how to use these functions, I have defined the following table and filled it with several rows of data:

Code

Use Northwind;
If   Object_id ( ' Tablea ' , ' U ' ) Is   Not   Null  
Drop   Table DBO. tablea;
Create   Table DBO. tablea
(
Cola Int   Identity ( 101 , 100 ) Not   Null ,
COLB Varchar ( 20 ) Not   Null
);
Insert   Into Tablea (COLB) Values ( ' Red ' );
Insert   Into Tablea (COLB) Values ( ' Blue ' );
Insert   Into Tablea (COLB) Values ( ' Green ' );
Insert   Into Tablea (COLB) Values ( ' Yellow ' );
Insert   Into Tablea (COLB) Values ( ' Orange ' );

Select * FromTablea;

Now, let's look at an example. We use several system functions to extract information about the data just inserted into the table above. The following SELECT statement contains five functions, three of which are related to the identity column value, and the other two are related to the number of rows:

Select  
Ident_current ( ' Tablea ' ) As Lastvalue,
Ident_seed ( ' Tablea ' ) As Seedvalue,
Ident_incr ( ' Tablea ' ) As Incrvalue,
@ Rowcount   As Rowsaffected,
Rowcount_big () As Bigrowsaffected;

Execution result:

The first function is ident_current, which extracts the last identity value of the inserted Table "tablea. As you can see, table "tablea" is specified as an input parameter of the function. The information returned by this function is irrelevant to the session when the data is added and the statement range when the data is added. This is important because SQL Server supports other identity-related functions, including functions dedicated to the current session (@ identity) and the current range and Session (scope_identity.

The ident_seed function returns the start value, while the ident_incr function returns the increment value.

@ Rowcount the function returns the number of rows affected by the last statement. The "rowcount_big" function does the same thing. The only difference is that the return value type is bigint. If you expect more than 2 billion rows to be affected, you should use this function. Note that these two functions are not associated with a table, because the two functions obtain data based on the last statement executed.

T-SQL system functions --- view server Properties

Sometimes you may want to retrieve specific server property values. In this case, you can use the "serverproperty" function. This function has a parameter (attribute name). See the following example:

Select  
Serverproperty ( ' Edition ' ) As Srvedition,
Serverproperty ( ' InstanceName ' ) As Srvinstance,
Serverproperty ( ' Collation ' ) As Srvcollation;

In this select statement, I retrieved the settings of the "edition", "instancename", and "collation" attributes, which were passed to the function as parameters. The "select" Statement returns the following results:

 

Some of the more common functions will not be summarized, very simple, give a book/files/gaoweipeng/transactionsql.pdf