T-SQL 实用函数总结

T-SQL是微软在关系型数据库管理系统SQL Server中的SQL-3标准的实现，是微软对SQL的扩展，具有SQL的主要特点，同时增加了变量、运算符、函数、流程控制和注释等语言元素，使得其功能更加强大。并且T-SQL自带了许多实用的函数，DBA可以利用这些函数来进行更有效地管理。

字符串函数

T-SQL字符串函数---截取字符串的值

下面的一组函数详细描述了如何截取字符串的一部分。这些函数大部分都包含在下面的SELECT语句中：

SELECT ProductName, 
LEFT(ProductName, 12) AS A, 
SUBSTRING(ProductName, 9, 4) AS B, 
RIGHT(ProductName, 2) AS C, 
RTRIM(ProductName) AS D 
FROM Products 

可以看到，SELECT语句中包括函数LEFT，SUBSTRING，RIGHT和RTRIM。执行结果：

LEFT函数返回ProductName列的前12个字符。该函数有两个参数：第一个参数是字符串表达式，第二个参数是字符数。在这个例子中，字符串表达式是ProductName列，字符数是“12”。结果，ProductName列所有值的后半部分都被截掉了，只剩下12个字符返回。

RIGHT函数的用法也是一样的，它截取字符串右边的部分。

SUBSTRING函数可以提取字符串的任意部分。该函数有三个参数：第一个参数是字符串表达式，第二个参数是指定从哪里开始截取，第三个参数表示截取多长的字符串。在上面的例子中，字符串表达式仍然是ProductName列。第二个参数“9”的意思是我们应该从第九个字符开始(截取)，第三个参数“4”意思是获取四个字符。

RTRIM的功能是去掉字符串(在第一个参数中指定)尾部的所有空格。RTRIM函数还有与它相对的另一个函数：LTRIM函数。它会删除字符串左侧的空格。

T-SQL字符串函数---修改字符串的值

T-SQL还包括可以在SQL Server中修改字符串值的一组函数。下面的语句就是一个例子：

SELECT语句中包括函数REPALCE，STUFF，REVERSE和REPLICATE，LOWER。执行结果：

 

函数REPLACE，用来替换给定字符串值中指定的一组字符串为新的字符串。该函数有三个参数：第一个参数是字符串表达式，第二个参数是被替换的字符串，第三个参数是要替换的新字符串。

函数STUFF，它的作用是按照给定的位置删除一组指定的字符，并插入一组新的字符。该函数有四个参数：第一个参数是待操作的字符串表达式，第二个参数是待删除的字符串的起始位置，第三个参数是要删除字符的数量，第四个参数是要插入的字符串。在上面的例子中，STUFF函数指定ProductName列为待操作的字符串。要删除的字符串从第11个字符(第二个参数)开始，删除两个字符(第三个参数)。在那些字符删除后，新字符(x01)被插入到该位置。

函数RESERVSE的作用是：把给定字符串按逆序排列。

函数REPLICATE，该函数可以把指定字符串值重复指定的次数。该函数有两个参数：第一个参数是待处理的字符串值，第二个参数是重复次数。在上面的例子中，“0”被重复了三次。

函数SPACE与函数REPLICATE类似，它返回一组空格。该函数有一个参数，指定返回的空格数量。

函数LOWER，很简单，它把字符串中的大写字符转换为小写字符。

T-SQL字符串函数---转换字符串值

T-SQL提供可以转换字符串值的函数，如下面的SELECT语句所示：

SELECT 
ProductName + CHAR(9) + STR(ProductID) AS Product, 
ASCII(ProductName) AsciiProductName, 
RTRIM(ProductName) AS ProductName, 
UNICODE(RTRIM(ProductName)) AS UnicodeProductName FROM Products

 SELECT语句中包括函数CHAR，STR，ASCII和RTRIM，UNICODE。执行结果：

第一个函数是CHAR，可以把一个ASCII码整数(从0到255)转换为字符，该函数只有一个参数(一个ASCII码整数值)。该函数在给你的字符串插入控制字符时非常方便，比如tab键(ASCII码为9)，换行符号(ASCII码为10)或者回车符(ASCII码为13)。

函数STR，它可以把数值型数据转换为字符型数据。STR函数的参数是待转换的数值型表达式。

T-SQL还支持ASCII函数，可以转换单个字符为ASCII码整数。

UNICODE函数，该函数用来把一个字符转换为Unicode整数。该函数只有一个参数：Unicode字符表达式。

T-SQL字符串函数---获取字符串的相关信息

还有一些函数可以提供与字符串有关的信息，比如字符串长度或者值内部子串的位置。下面的SELECT语句就演示了几个提供字符串相关信息的函数：

SELECT LEN(ProductName) AS LengthProductName, 
CHARINDEX('Bob', ProductName) AS CharIndexProductName,
PATINDEX('%Pears%', ProductName) AS PatIndexProductName 
FROM Products WHERE ProductID=7

SELECT语句中包括函数CHAR，STR，ASCII和RTRIM，UNICODE。执行结果：

 

函数LEN返回指定字符串的字符数量。该函数只有唯一参数就是待计数的字符串。

函数CHARINDEX的功能是从某字符串中指定的开始位置查找某字符串所在位置。该函数有三个参数：第一个参数是要搜索的字符串，第二个参数是待搜索范围字符串，第三个参数是搜索开始的位置。最后一个参数是可选的。在上面的例子中，我们在ProductName列中搜索Bob第一次出现的位置，它是从字符串的第7个位置开始的。

函数PATINDEX。该函数在字符串中搜索第一次出现某字符串的位置。该函数有两个参数：第一个参数是要匹配的字符串，第二个参数是在其中进行搜索的字符串。可以用通配符定义匹配模式，提供灵活的搜索模式类型。然而，如果你使用通配符“%”，你必须在待搜索字符的前面或者后面都加上它，除非你搜索以指定字符串开头或结尾的匹配。例如，在上面的语句中，“%Pears%”被指定为在ProductName值中搜索的匹配模式。结果显示，第一次匹配的实例在第27个字符位置。

T-SQL集合函数

T-SQL集合函数---基础函数

在操作SQL Server数据库中的数据时，有时你可能会需要对一组数值执行计算。此处灰常基础，就不过多说了。

MAX函数：最大值函数。MIN函数：最小值函数。AVG函数：平均值函数。SUM函数：求和函数。COUNT函数：总计函数。

利用集合函数处理分组数据的时候，可以使用ROLLUP，CUBE或者GOURPING SETS操作符来指定包含在结果集中的附加统计数据。

T-SQL集合函数---获取统计数据

SQL Server有四个集合函数可以支持你对数值型列的一组值做数据统计：

STDEV：返回值的统计标准方差。
STDEVP：返回总体值的统计标准方差。
VAR：返回值的统计方差。
VARP：返回总体值的统计方差。

SELECT STDEV(UnitPrice) AS StdevPrice, 
STDEVP(UnitPrice) AS StdevpPrice, 
VAR(UnitPrice) AS VarPrice, 
VARP(UnitPrice) AS VarpPrice FROM Products

执行结果：

T-SQL 系统函数 

SQL Server内建有可以支持你执行各种任务的函数，比如转换数据，操作字符串值，以及执行数学计算的函数。一些SQL Server函数被归类为系统函数，这些函数支持你查看关于更新和插入操作的信息，提取服务器属性设置，访问当前会话及其用户的详细信息。你还可以利用系统函数执行许多语言相关的任务。

T-SQL系统函数---查看有关数据修改的信息 

SQL Server的一些系统函数支持获取你的更新和插入操作的明细信息。例如，你可以获取插入到某个表中的最后一个标识值，或者对某个表增加或者更新的行数。要展示这些函数如何使用，我定义了下面的表并给它填充了几行数据：

 现在，我们来看一个例子，其中使用了几个系统函数提取关于刚插入上面那个表的数据相关的信息。下面的“SELECT语句”包括五个函数，其中三个与身份列值有关，而另两个与行数有关：

SELECT 
IDENT_CURRENT('TableA') AS LastValue, 
IDENT_SEED('TableA') AS SeedValue,
IDENT_INCR('TableA') AS IncrValue, 
@@ROWCOUNT AS RowsAffected, 
ROWCOUNT_BIG() AS BigRowsAffected;

执行结果：

第一个函数是IDENT_CURRENT，它提取插入表“TableA”的最后一个身份值。如你所见，表“TableA”被指定为该函数的一个入参。该函数返回的信息与添加数据时的会话无关，也与添加时执行的语句范围无关。这一点非常重要，因为SQL Server支持其他身份相关的函数，包括专门针对当前会话(@@IDENTITY)和当前范围和会话(SCOPE_IDENTITY)的函数。

IDENT_SEED函数返回起始值，而IDENT_INCR函数返回增量值。

@@ROWCOUNT 函数返回最后一个语句影响的行数。“ROWCOUNT_BIG”函数也做同样的事情，唯一区别是返回值类型是bigint。如果你预计影响的行数会多于20亿条数据，你应该使用这个函数。要注意，这两个函数都不是关联于某个表的，因为这两个函数基于最后一条执行的语句获取数据。

T-SQL系统函数---查看服务器属性

有时，你可能想要检索特定的服务器属性值，这种情况下，你可以使用“SERVERPROPERTY”函数。该函数有一个参数(属性名称)，请看下面的示例：

SELECT 
SERVERPROPERTY('Edition') AS SrvEdition, 
SERVERPROPERTY('InstanceName') AS SrvInstance, 
SERVERPROPERTY('Collation') AS SrvCollation;

在这个“SELECT”语句中，我检索了“Edition”，“InstanceName ”和“Collation ”属性的设置，它们以参数的形式传递到函数中。该“SELECT”语句返回下面结果：