【SQL Server学习笔记】SQL Server系统函数

angela

　　SQL Server的系统函数，可以让我们实现聚合、数学运算、字符串操作、日期运算、NULL的处理、排序等功能。
　　需要注意的是SQL Server的系统函数和用户自定义函数是不同的，系统函数是SQL Server内部支持的函数，不需要我们先定义，再使用。
　　我们通过Microsoft SQL Server Management Studio客户端工具，可以看到SQL Server提供的系统函数，如下图：

http://img.blog.csdn.net/20131022152357812?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvc3Fsc2VydmVyZGlzY292ZXJ5/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center

　　下面，通过实例，来说明如何使用SQL Server的内建函数：
　　1、SQL Server元数据函数
--1.列 --列的长度 select COL_LENGTH('dbo.wct',  --表名                   'wcid')     --列名  --列名 select COL_NAME(object_id('dbo.wct'),  --表ID                 1)                     --列ID  /*============================================ 列属性： ColumnId   AllowsNull  Precision Scale  IsComputed IsSparse IsCursorType  IsColumnSet   IsIdentity IsRowGuidCol   FullTextTypeColumn  IsFulltextIndexed  IsIdNotForRepl  IsIndexable IsOutParam IsXmlIndexable   IsDeterministic IsPrecise IsSystemVerified  SystemDataAccess  UserDataAccess  UsesAnsiTrim ==============================================*/ --columnProperty函数返回有关列过程参数的信息 select ColumnProperty(object_id('dbo.wct'), --表ID                       'wcid',               --列名                       'precision')          --属性名   --2.数据库 --DatabasePropertyEx函数返回指定数据库中指定选项的属性，要返回更多可以用sys.databases目录视图 select DatabasePropertyEx('master',      --数据库名                           'IsAutoClose') --属性名   --db_id函数返回指定数据库名称对应的id select db_id('master')      --db_name函数返回指定数据库id号的数据库名称 select db_name(1)    --3.文件与文件组 --file_id函数返回指定逻辑文件名对应的id,以后版本将删除此函数 select  file_id('wc_fg5_1')   --文件逻辑名  --建议采用此函数 select FILE_IDEX('wc_fg5_1')  --filegroup_id函数返回指定文件组名称对应的id select filegroup_id('wc_fg5')   --filegroup_name函数返回指定文件组id对应的文件组名称 select filegroup_name(6)   --FilegroupProperty函数返回指定文件组中相应选项的属性， --要返回更多可以用sys.filegroups目录视图 select FilegroupProperty('wc_fg5',                          'IsUserDefinedFG') --是否是用户定义的文件组  select FilegroupProperty('wc_fg5',                          'IsDefault')   --是否是默认文件组  select FilegroupProperty('wc_fg5',                          'IsReadOnly')  --是否只读                            --file_name函数返回指定逻辑文件对应的逻辑文件名称 select  file_name(8)  --FileProperty函数返回指定逻辑文件相应选项的属性 select  fileproperty('wc_data',                              'IsPrimaryFile')                       select  fileproperty('wc_fg5_1',                              'IsReadOnly')                         select  fileproperty('wc_fg5_1',                              'SpaceUsed') --返回在文件中分配的页数   select  fileproperty('wc_log1',                              'IsLogFile') --是否日志文件                                                              --4.对象 --object_id函数返回架构范围内对象的数据库对象id select object_id('master.dbo.spt_values')   select object_id('master.dbo.spt_values','U')   --object_name函数返回架构范围内对象id对应的数据库对象名称 select object_name(1115151018)   --objectPropertyEx函数返回架构范围内对象相应选项的属性，返回更多可以用sys.objects系统目录 select ObjectPropertyex(object_id('spt_values'),                         'IsTable')  select ObjectPropertyex(object_id('wct'),                         'TableHasForeignKey')--有FOREIGN KEY约束的表  select ObjectPropertyex(object_id('wct'),                         'TableUpdateTriggerCount')--update触发器的个数  select ObjectPropertyex(object_id('wct'),                         'IsTrigger') --是否是触发器                          select ObjectPropertyex(object_id('wct'),                         'TableHasPrimaryKey')         --对象是否有主键    --5.索引 --索引中指定键id对应的列名 select INDEX_COL('wc.dbo.wcT',   --数据库.架构.表                  1,              --索引id                   1)              --索引中键的id  --索引键的属性 select INDEXKEY_PROPERTY(OBJECT_ID('wc.dbo.wcT'), --对象id                          1,                       --索引id                          2,                       --键id                          'ColumnId')              --对应到表中的列id                             select INDEXKEY_PROPERTY(OBJECT_ID('wc.dbo.wcT'), --对象id                          1,                       --索引id                          2,                       --键id                          'IsDescending')          --是否降序  --索引属性 select INDEXPROPERTY(object_id('wc.dbo.wcT'),                      'pk_wcid_date',                      'IndexID')  --索引id  select INDEXPROPERTY(object_id('wc.dbo.wcT'),                      'pk_wcid_date',                      'IsClustered') --是否聚集  select INDEXPROPERTY(object_id('wc.dbo.wcT'),                      'pk_wcid_date',                      'IsDisabled')  --是否禁用  select INDEXPROPERTY(object_id('wc.dbo.wcT'),                      'pk_wcid_date',                      'IsRowLockDisallowed') --是否允许行级锁  select INDEXPROPERTY(object_id('wc.dbo.wcT'),                      'pk_wcid_date',                      'IsStatistics')  --是否自动创建统计信息  select INDEXPROPERTY(object_id('perfetti.dbo.wcT'),                      'idx_wc',                      'IndexDepth')  --索引深度      --6.类型AllowsNull select TYPEPROPERTY('int', --类型名                     'Precision') --类型属性                      select TYPEPROPERTY('int', --类型名                     'AllowsNull') --类型属性                                            --7.当前模块的对象ID,可以是存储过程,用户定义函数,触发器 select @@PROCID  --8.扩展属性 select * from ::fn_listextendedproperty(default,                                         'schema',    --架构                                         'dbo',       --架构名                                          'table',     --表                                            'wcT',       --表名                                         default,    --列                                         default      --列名                                         )   DECLARE @V SQL_VARIANT SET @V = 'ABCEFG'  select SQL_VARIANT_PROPERTY(@V,                             'BaseType')      select SQL_VARIANT_PROPERTY(@V,                             'TotalBytes')   select SQL_VARIANT_PROPERTY(@V,                             'MaxLength')     --9.服务器属性 select SERVERPROPERTY('Collation'),        SERVERPROPERTY('IsClustered'),        SERVERPROPERTY('IsSingleUser'),        SERVERPROPERTY('MachineName'),        SERVERPROPERTY('ProcessID'),        SERVERPROPERTY('NumLicenses'),        SERVERPROPERTY('SqlSortOrder')   --10.会话属性 select SESSIONPROPERTY('ANSI_NULLS'),        SESSIONPROPERTY('ANSI_WARNINGS'),        SESSIONPROPERTY('ANSI_PADDING'),        SESSIONPROPERTY('ARITHABORT'),        SESSIONPROPERTY('NUMERIC_ROUNDABORT'),        SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL'),        SESSIONPROPERTY('QUOTED_IDENTIFIER')　　2、聚合函数
--基本聚合函数 SELECT MAX(wcId),        MIN(WCID),        COUNT(WCiD),        COUNT_BIG(wcId),        COUNT(*),        SUM(WCiD),        AVG(WCID)             FROM dbo.wct   use AdventureWorks go  /*==================================================== x1,x2,...,xn共n个数，m是这n个数的平均数     var：square(x1-m)+square(x2-m)+...+square(xn-m)。   varp：var/n   stdev:sqrt(var) stdevp:sqrt(varp)         ======================================================*/ select VAR(taxAmt),        VARP(taxAmt),                STDEV(taxAmt),        STDEVP(taxAmt),                case when STDEV(taxAmt) = sqrt(VAR(taxAmt))                  then 'equal'             else 'not equal'        end,                       case when STDEVP(taxAmt) = sqrt(VARP(taxAmt))                  then 'equal'             else 'not equal'        end,                checksum_agg(SalesOrderID)  --返回组中各值的校验和       from sales.SalesOrderHeader 　　3、数学函数
SELECT ABS(-1),      --绝对值        CEILING(1.56),--大于指定值的最小整数        FLOOR(1.56),  --小于指定值的最大整数        RAND(10),     --返回0~1之间的浮点数，如果要每次返回相同的数,可以指定seed                      --如未指定seed，则SQL Server数据库引擎随机分配种子值        ROUND(28.9,0,0),--第三个参数指定0时表示四舍五入,1表示截断（不会进位）        ROUND(28.9,0,1),                  LOG(2),                 --指定参数的对数        EXP(0.693147180559945), --指定参数的指数        LOG10(2),               --指定参数的自然对数        POWER(2,10),            --第一个参数对第二个参数的幂        SQUARE(2),              --平方        SQRT(2),                --开方                 PI(),               --PI常量3.1415926        DEGREES(PI()),      --把弧度转为角        RADIANS(180.0),     --把角度转为弧度,返回值的类型与输入参数类型相同                SIN(1),                 --指定角或弧度的正弦值        ASIN(0.841470984807897),--由参数所指定的正弦值,来计算其所对应的角或弧度                       COS(1),                 --指定角或弧度的余弦值        ACOS(0.54030230586814), --由参数所指定的余弦值,来计算其所对应的角或弧度                       TAN(1),                 --指定角或弧度的正切值        ATAN(1.5574077246549),  --由参数所指定的正切值,来计算其所对应的角或弧度                  --指定角或弧度的余切值                  COT(1),            --返回以弧度表示的角，该角位于正 X 轴和原点至点 (y, x) 的射线之间，        --其中 x 和 y 是两个指定的浮点表达式的值。        ATN2(1,2) 　　4、字符串函数
--1.字符转化为ASCII,把ASCII转化为字符,注意返回的值是十进制数 select ASCII('A'),ASCII('B'),ASCII('a'),ASCII('b'),ASCII('?')  select CHAR(65),CHAR(66),CHAR(97),CHAR(98),CHAR(63)   --2.unicode字符转化为整数,把整数转化为unicode字符 select UNICODE('A'),UNICODE('B'),UNICODE('a'),UNICODE('b'),UNICODE('你')  select NCHAR(65),NCHAR(66),NCHAR(97),NCHAR(98),NCHAR(20320)   --3.根据英语发音规则来评价字符串的发音相似度 select SOUNDEX('Fleas'),        SOUNDEX('Fleece'),        SOUNDEX('Peace'),        SOUNDEX('Peas')         select DIFFERENCE('Fleece','Fleas') --返回0~4之间的值,返回值越大,那么越相似   --4.字符串大小写转换 select UPPER('the company is very large!'),        LOWER('THE CORPORATION IS VERY LARGE!')  --5.重复一个字符串N次 SELECT REPLICATE('wc ',                  5)  --6.重复一个空格N次 select SPACE(20)   --7.获取字符串最左和最右部分 SELECT left('the company is very large!',11),        right('the company is very large!',6)  --8.去掉字符串最左，最右的空格 select RTRIM('   the company is very large!'),        LTRIM('the company is very large!   ')   --9.1返回一个字符串在另一个字符串中的起始位置 select CHARINDEX('string to find',                  'this is the bigger string to find something in.',                  1                 )  --9.2使用通配符查找,返回一个字符串在另一个字符串中的起始位置 select PATINDEX('%string%',                 'this is the bigger string to find something in.'                 )  --10.返回字符串的子串 select SUBSTRING('the company is very large!',                  5,                  3)   --11.返回字符串的字符个数,返回字符串的字节数 select len('the company is very large!'),         DATALENGTH(N'the company is very large!')   --12.把字符串的一部分替换成另一个字符串 select REPLACE('the company is very large!',                'company',                'corporation')  --13.把字符串中的一部分填充成另一个字符串 select STUFF('the company is very large!',              charindex('large','the company is very large!'),              LEN('large'),              'good'             )             --14.返回逆序的字符串 select REVERSE('the company is very large!')   --15.把数字转化成字符数据,会四舍五入 --要是总长不能满足小数点位数的要求,那么只显示整数部分 select STR(-123456.623,            7           --这个总长度包括小数点、负号,           )              select STR(123456.653,            8,           --总长度,会四舍五入            2            --数值范围，小数点后面的位数           )                 --16.为输入的UNICODE字符串增加分隔符 select QUOTENAME('abc','"')      --分隔符"   select QUOTENAME('abc','''')     --分隔符'              　　5、日期函数
--1.返回当前日期和时间 select GETDATE()          '当前日期-精确到33毫秒'  select GETUTCDATE()       'UTC日期和时间-精确到33毫秒'  select SYSDATETIME()      '当前日期和时间-精确到100纳秒(高精度)'         select SYSUTCDATETIME()   'UTC-精确到100纳秒(高精度)'        select SYSDATETIMEOFFSET()'当前日期与UTC之间的差值(时分)'   /*====================================================== 2.转换偏移，从一个转为另一个,从UTC-05:00转为UTC+03:00:  '2007-08-12 10:20:35.9788989'是UTC减去'05:00'之后算出来的， 所以UTC是'2007-08-12 10:20:35.9788989'加'05:00'， 也就是'2007-08-12 15:20:35.9788989'， 然后'2007-08-12 15:20:35.9788989'加上'03:00'， 也就是'2007-08-12 18:20:35.9788989' ========================================================*/ select SWITCHOFFSET('2007-08-12 10:20:35.9788989 -05:00','+03:00')  --将普通的日期时间型转为日期时间偏移，只是在日期时间后增加了offset值 --返回:2007-08-12 10:20:35.0000000 -05:00 select TODATETIMEOFFSET('2007-08-12 10:20:35','-05:00')     --3.显示日期一部分的字符串 select SYSDATETIME(),        DATENAME(year,SYSDATETIME()),        DATENAME(quarter,SYSDATETIME()),        DATENAME(month,SYSDATETIME()),                DATENAME(dayofyear,SYSDATETIME()),        DATENAME(day,SYSDATETIME()),                DATENAME(week,SYSDATETIME()),        DATENAME(weekday,SYSDATETIME()),                DATENAME(hour,SYSDATETIME()),        DATENAME(minute,SYSDATETIME()),        DATENAME(second,SYSDATETIME()),               DATENAME(millisecond,SYSDATETIME()),        DATENAME(microsecond,SYSDATETIME()),               DATENAME(nanosecond,SYSDATETIME()),                DATENAME(TZoffset,SYSDATETIME()),        DATENAME(ISO_WEEK,SYSDATETIME())        --4.显示日期一部分的整数 select YEAR(GETDATE()),        MONTH(getdate()),        DAY(getdate())  select SYSDATETIME(),        DATEPART(year,SYSDATETIME()),        DATEPART(quarter,SYSDATETIME()),        DATEPART(month,SYSDATETIME()),                DATEPART(dayofyear,SYSDATETIME()),        DATEPART(day,SYSDATETIME()),                DATEPART(week,SYSDATETIME()),        DATEPART(weekday,SYSDATETIME()),                DATEPART(hour,SYSDATETIME()),        DATEPART(minute,SYSDATETIME()),        DATEPART(second,SYSDATETIME()),                DATEPART(millisecond,SYSDATETIME()),        DATEPART(microsecond,SYSDATETIME()),               DATEPART(nanosecond,SYSDATETIME()),        DATEPART(TZoffset,SYSDATETIME()),        DATEPART(ISO_WEEK,SYSDATETIME())                        --5.增加、减少日期值 SELECT DATEADD(quarter, 1, getdate()),                         DATEADD(quarter,-1, getdate())                       --6.计算两个日期的差值 select DATEDIFF(DAY,'2011-08-09',GETDATE()),        DATEDIFF(MONTH,'2011-08-09',GETDATE())  　　6、处理NULL的函数
DECLARE @V1 INT,@V2 INT,@v3 numeric(10,0)  SELECT @V1 = 10,        @V2 = NULL,        @v3 = 0  SELECT ISNULL(@v1,0),         --如果参数一为null,那么返回参数二,否则返回参数一        ISNULL(@v2,0),                coalesce(@V1,@v2,@v3), --返回第一个非NULL值        coalesce(@v2,@v3,@v1),                nullif(@V3,100),    --如果参数一等于参数二，那么返回NULL,否则还是返回参数一        100 / nullif(@v3,0) --用来解决除数为0的问题   　　7、类型转化函数
--1.基本的转化 SELECT CAST(2008 as varchar(4)) + ' year!'  SELECT CONVERT(varchar(4),2008) + ' year!'   --2.把日期转化为文本 SELECT CONVERT(VARCHAR(30),GETDATE(),120)  --年-月-日 时:分:秒(24h)  SELECT CONVERT(VARCHAR(10),GETDATE(),120)  --年-月-日 时:分:秒(24h)   SELECT CONVERT(VARCHAR(8),GETDATE(),108)   -- 时:分:秒(24h)   select CONVERT(varchar(30),getdate(),121)  --年-月-日 时:分:秒 时:分:秒.毫秒(24h)   --3.使用不同样式转换二进制和字符数据的结果 SELECT CONVERT(varbinary,                'SQL Server 2008 T-SQL Recipes',                0   --表示把字符串转化为二进制               )  select CONVERT(char(29),                0x53514C20536572766572203230303820542D53514C2052656369706573,                0   --把二进制转化为ASCII格式的字符串               )            --下面两个都是转化为字面量                SELECT CONVERT(CHAR(60),                0x53514C20536572766572203230303820542D53514C2052656369706573,                1   --转化为16进制字面量格式,带0x前缀               )  SELECT CONVERT(CHAR(60),                0x53514C20536572766572203230303820542D53514C2052656369706573,                2               )    --转化为16进制字面量格式,不带0x前缀           --4.计算表达式返回的类型 select ISDATE('20000-01-01')  select ISDATE('2000-01-01')       --字符会返回0 SELECT ISNUMERIC('ABC')  --如果数字之间有空格，也会返回0 SELECT ISNUMERIC('12 3')   --如果数字之间有其他符号，也会返回0 SELECT ISNUMERIC('12,3')  --如果数据末尾,开头有空格,不会有影响,还是返回1 SELECT ISNUMERIC(' 12,3 ')       　　这里需要特别提到的是，在原来的公司，曾经遇到过日期转换的问题，由于把日期数据存储在了varchar类型中，导致转换报错，那么这种情况，如何处理呢？
　　下面通过一个简单的例子来说明处理方法：
if object_id('a') is not null    drop table a  go  create table A(Id int, date varchar(30))  insert into A select 1,   '2013-10-40 00:00:00' union all select 2,   '2013-10-01  12:00:00' union all select 3,   '2014-01-01 12:09:34'  --报错了 select * from A where Isdate(date) = 1 and Convert(datetime,date) >= '2014-01-01 00:00:00' /*>　　如何把负数转化为16进制数？
--1.一开始这么转化,但是有问题,关键在于你的这个数太大,超出了int的范围 select cast(-2161622263693857431 as varbinary) /* 0x1300000097520CFEFE9FFF1D */   --2.这样就行了,转化为bigint,在转为varbianry,但是这个不是字符串,所以还是不行 select cast(cast(-2161622263693857431 as bigint) as varbinary)  /* 0xE200600101F3AD69 */    use 你的数据库 go  IF OBJECT_ID ('dbo.varbin2hexstr') IS NOT NULL    DROP FUNCTION dbo.varbin2hexstr GO           --这个函数实现了把varbinary类型数据转化为varchar类型的数据     CREATE function varbin2hexstr( @bin varbinary(max) )returns varchar(max) as begin     declare @re varchar(max),@i int     select @re='',@i=datalength(@bin)     while @i>0         select @re=substring('0123456789ABCDEF',substring(@bin,@i,1)/16+1,1)                 +substring('0123456789ABCDEF',substring(@bin,@i,1)%16+1,1)                 +@re             ,@i=@i-1    -- return('0x'+@re)     return @re end      GO   --3.调用函数dbo.varbin2hexstr,把varbinary转为字符串,这样才行 select dbo.varbin2hexstr(cast(cast(-2161622263693857431 as bigint) as varbinary)) /* E200600101F3AD69 */ 　　8、排名函数
select * into objectT from sys.objects  insert into objectT select top (10) * from sys.objects  --生成递增行号 select object_id,        name,        ROW_NUMBER() over(order by object_id) from objectT   --排名,会有间隔,比如排第1的有2行数据,那么接下来直接就是排在第3的 select OBJECT_ID,        name,        RANK() over(order by object_id) from objectT   --无间隔排名 select OBJECT_ID,        name,        dense_rank() over(order by object_id) from objectT   /*=============================================== 根据排序,可选的分区，把结果集分成指定数量的分组 ntile(n) partition by x order by y:  按照x分组，然后按y排序，然后把每个分组分成n个分组 =================================================*/ select OBJECT_ID,        name,        type_desc,        NTILE(3) over(partition by type_desc                          order by object_id) from objectT 　　9、系统统计函数
select @@SPID,        --返回当前连接的会话ID:SPID         @@servername,  --SQL Server实例名称        @@sevicename,  --SQL Server版本信息         APP_NAME(),    --客户端连接的应用程序名称        HOST_ID(),     --客户端连接的进程ID:936               HOST_NAME(),   --客户端连接的主机名称:PC0627JVC                system_user,   --登录名:PC0627JVC\Administrator        user           --数据库用户:dbo   --设置一周中的第一天 set datefirst 7  select @@DATEFIRST '一周中的第一天'   --设置当前会话的语言 set language '简体中文'  select @@LANGID '语言id',        @@LANGUAGE '当前会话的语言'          --设置当前连接的锁超时设置 set lock_timeout 1000  select @@LOCK_TIMEOUT   --设置之前语句影响的行数 set rowcount 20  select @@ROWCOUNT   --返回打开事务的个数 select @@TRANCOUNT   --显示当前存储过程上下文的嵌套级别 --SQL Server允许存储过程最多32次嵌套调用 --也就是在一个存储过程里调用另一个存储过程，最多32次 select @@NESTLEVEL   --统计信息,属性信息 select         @@CPU_BUSY,        @@IO_BUSY,         @@IDLE,                @@error,              @@PACKET_ERRORS,        @@PACK_RECEIVED,        @@PACK_SENT,               @@TOTAL_ERRORS,        @@TOTAL_READ,        @@TOTAL_WRITE,                                     @@CURSOR_ROWS,        @@FETCH_STATUS,        @@IDENTITY,        @@OPTIONS,        @@CONNECTIONS,        @@MAX_CONNECTIONS,        @@MAX_PRECISION,               @@MICROSOFTVERSION,        @@VERSION,        @@PROCID,        @@REMSERVER,        @@TEXTSIZE,        @@TIMETICKS,        @@DBTS,        @@DEFAULT_LANGID,        @@DEF_SORTORDER_ID