系统函数和自定义函数

从功能方面来划分，SQL Server中的函数主要分为：字符串函数、数学函数、文本和图像函数、日期和时间函数以及其他函数。

一.系统函数

所谓系统函数，就是安装SQL Server后就有的函数，可以直接使用，SQL Servere中的系统函数包括字符串函数、数学函数、时间和日期函数等。
1.字符串函数

--字符串函数
SELECT ASCII('AB'),ASCII('P'),ASCII(2)

--UPPER转换为大写,LOWER转换为小写
SELECT UPPER('abc'),UPPER('dlm'),LOWER('ABC'),LOWER('BLM')

--RIGHT（x,y）从右边开始,截取x字符y个,LEFT（x,y）从左边开始,截取x字符y个
SELECT RIGHT('abcdefg',4),LEFT('abcdefg',4)

--LEN字符串的长度,REVERSE逆序输出字符串
SELECT LEN('ABCDEFGHIJK'),REVERSE('ABCDEFGHIJK')

--CHARINDEX（x,y,[z]）从y字符中找x字符位置，如果参数z存在，那就从z提供的数字处开始查找
SELECT CHARINDEX('a','banana'),CHARINDEX('a','banana',4),CHARINDEX('na','banana',4)

--REPLACE(x,y,z)字符串替换，x字符串中，把y字符串替换为z字符串
SELECT REPLACE('abcdefgabcdefg','a','A')

2.数学函数

--SQUARE(x)x的平方,SQRT(x)x的平方根，PI()Π（3.14159265358979）
SELECT SQUARE(5),SQRT(81),PI()

--POWER(x,y)，2的4次方
SELECT POWER(2,4)
--CEILING 函数返回大于或等于所给数字表达式的最小整数。
--FLOOR 函数返回小于或等于所给数字表达式的最大整数。
SELECT CEILING(-4.35),CEILING(3.35)
SELECT FLOOR(-4.35),FLOOR(3.35)

3.日期时间函数

--GETDATE获取系统当前时间
SELECT  GETDATE(),YEAR(GETDATE())

--DateADD（day,4,GETDATE），天+4天，在当前时间上添加
SELECT DATEADD(DAY,4,GETDATE())

--DATEDIFF（YEAR,GETDATE(),'1998-1-12'），1998年减当前时间年份。
SELECT DATEDIFF(YEAR,GETDATE(),'1998-1-12')

--DATEPART,返回日期中指定部分的整数值
SELECT DATEPART(YEAR,'2018-11-1'),DATEPART(MONTH,'2018-11-1'),DATEPART(DAY,'2018-11-1')

4.系统信息函数

--系统信息函数DB_ID(x)x数据库的ID,DB_NAME(x)无x参数，当前数据库名称，有x参数，xID数据库名称
--HOST_ID()当前服务器端计算机的标识号，HOST_NAME()当前计算机名称
SELECT DB_ID('my_db'),DB_NAME(15),HOST_ID(),HOST_NAME()

--USER_ID()当前用户的数据库标识号，USER_NAME()当前数据库用户名称
SELECT USER_ID(),USER_NAME()

5.类型转换函数

--CAST,CONVERT
--CAST转换日期函数格式是不能指定的，而CONVERT能指定转换的格式
SELECT CAST('3.1415' AS DECIMAL(3,2)),CONVERT(DECIMAL(3,2),'3.1415')

二.自定义函数

用户自定义函数可以像系统函数一样在查询或存储过程中调用也可以像存储过程一样使用EXECUTE命令来执行。于编程语言中的函数类似，SQL Server用户自定义函数可以接受参数、执行操作并将结果以值形式返回。
1.自定义函数
根据自定义函数的功能，一般可以将自定义函数分为两种，一种是标量函数，另一种是表值函数，常用的自定义函数是标量函数。
（1）标量值函数（返回一个标量值）
（2）表格值函数（内联表格值函数、多语句表值函数，返回一个结果集即返回多个值）
2.标量函数

--自定义函数语法
CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS date_type   --返回返回值的数据类型
[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
[AS]
BEGIN
  function_body --函数体(即 Transact-SQL 语句)
  RETURN  表达式;
END

--创建带参数的标量函数
CREATE FUNCTION datefun(@date VARCHAR(20))
    RETURNS VARCHAR(20)
BEGIN
    DECLARE @tb VARCHAR(20)
    SELECT @tb=e_name FROM employee WHERE hireDate=@date
    RETURN @tb
END
--使用自定义函数数需要在前面加dbo
select dbo.datefun('2018-05-18')
结果：李旭红

2.表值函数
使用表值函数，一般是为了完成根据某一个条件，查询处相对应的查询结果。

CREATE FUNCTION datefunS(@date VARCHAR(20))
    RETURNS TABLE
    RETURN SELECT * FROM employee WHERE hireDate=@date
--表值函数使用方法和表相似
select  * from datefunS('2018-05-18')

3.修改自定义函数
自定义函数的修改与创建语句很相似，只是将创建自定义函数语法中的CREATE语句换成ALTER语句就行。

ALRTER FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS date_type   --返回返回值的数据类型
[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
[AS]
BEGIN
  function_body --函数体(即 Transact-SQL 语句)
  RETURN  表达式;
END

4.删除自定义函数

DROP FUNCTION dbo.fun_name