1、常用函数的定义和类别
我们从数据表中SELECT查询数据时，就需要对数据进一步处理，获得符合期望的结果。这些处理就包括：执行计算与数学运算、转换数据、解析数值、组合值和聚合一个范围内的值等等。
函数类别 | 说明 |
聚合函数 | 执行的操作是将多个值合并为一个值。例如 COUNT、SUM、MIN 和 MAX。 |
配置函数 | 是一种标量函数，可返回有关配置设置的信息。 |
转换函数 | 将值从一种数据类型转换为另一种。 |
加密函数 | 支持加密、解密、数字签名和数字签名验证。 |
游标函数 | 返回有关游标状态的信息。 |
日期和时间函数 | 可以更改日期和时间的值。 |
数学函数 | 执行三角、几何和其他数字运算。 |
元数据函数 | 返回数据库和数据库对象的属性信息。 |
排名函数 | 是一种非确定性函数，可以返回分区中每一行的排名值。 |
行集函数 | 返回可在 Transact-SQL 语句中表引用所在位置使用的行集。 |
安全函数 | 返回有关用户和角色的信息。 |
字符串函数 | 可更改 char、varchar、nchar、nvarchar、binary 和 varbinary 的值。 |
系统函数 | 对系统级的各种选项和对象进行操作或报告。 |
系统统计函数 | 返回有关 SQL Server 性能的信息。 |
文本和图像函数 | 可更改 text 和 image 的值。 |
2、字符串函数
函数 | 说明 |
ASCII() | ASCII(@char)函数用于返回字符串表达式中最左侧的ASCII代码值。参数@char必须是一个char或varchar类型的字符串表达式。 |
CHAR() | CHAR(@integer)函数将整数类型的ASCII值转换为对应的字符，@integer是一个介于0~255之间的整数。如果该整数表达式不在此范围内，将返回null值。 |
LEFT() | LEFT(@char,@integer)函数返回字符串左边开始指定个数的字符串、字符或者二进制数据表达式。@char是字符串表达式，可以是常量，变量或字段。@integer为整数，指定@char将返回的字符数。 |
RIGHT() | 与LEFT()函数相反，RIGHT(@char,@integer)返回字符串@char最右边@integer个字符。 |
LTRIM() | LTRIM(@char)用于除去字符串左边多余的空格，字符数据表达式@char是一个字符串表达式，可以是常量，变量，也可以是字符字段或者二进制数据列。 |
RTRIM() | RTRIM(@char)用于除去字符串右边多余的空格，字符数据表达式@char是一个字符串表达式，可以是常量，变量，也可以是字符字段或者二进制数据列。 |
STR() | STR(@float [ , length [ , decimal ] ])函数用于将数值数据转换为字符数据。@float是一个带有小数点的近似数字（float）数据类型的表达式。length表示总长度。它包括小数点、符号、数字以及空格，默认值为10。decimal指定小数点后的位数，decimal必须小于或等于16。如果decimal大于16，则会截断结果，使其保持小数点后有16位。 |
REVERSE() | REVERSE(s)将字符串s反转，返回的字符串的顺序和s的顺序相反。 |
LEN() | 返回字符表达式中的字符数。如果字符串中包含前导空格和尾随空格，则函数将它们包含在内。LEN对相同的单字节和双字节字符串返回相同的值。 |
CHARINDEX() | CHARINDEX(str1,str,[start])函数返回子字符串str1在字符串str中的开始位置，start为搜索的开始位置，如果指定start参数，则从指定位置开始搜索；如果不指定start参数或者指定为0或者负值，则从字符串开始位置搜索。 |
SUBSTRING() | SUBSTRING(@value,@start,@length)函数返回字符表达式，二进制表达式，文本表达式或图像表达式的一部分。 @value是character、binary、text、ntext或image表达式。 @start指定返回字符的起始位置的整数或表达式。如果@start小于0，或生成错误并终止语句。如果 @start大于值表达式的字符数，将返回一个零长度的表达式。 @length是正整数或指定要返回的@value的字符数表达式。如果@length是负数，会生成错误并终止语句，如果@start与@length的总和大于@value中的字符数，则返回整个值表达式。 |
LOWER() | LOWER(@char)将大写字符数据转换为小写字符数据后返回字符表达式。@char是指定要进行转换的字符串。 |
UPPER() | UPPER(@char)将小写字符数据转换为大写字符数据后返回字符表达式。@char指定要进行转换的字符串 |
REPLACE() | REPLACE(s,s1,s2)使用字符串s2替代字符串s中的s1。 |
执行代码：
（1）ASCII()函数
SELECT ASCII('h'),ASCII('H'),ASCII('5') 
（2）CHAR()函数
SELECT char(50),char(60),char(115)
（3）LEFT()函数
SELECT LEFT('BASKETBALL',5)
（4）RIGHT()函数
SELECT RIGHT('BASKETBALL',5)
（5）LTRIM()函数
select '(' +' book ' +')' , '(' +LTRIM(' book ') +')'
（6）RTRIM()函数
select '(' +' book ' +')' , '(' +RTRIM(' book ') +')'
（7）STR()函数
select STR(3141.59,6,1) , STR(123.45,2,2)
说明：第一条语句6个数字和一个小数点组成的数值3141.59转换为长度为6的字符串，数字的小数部分舍入为1个小数位，第二条语句中的表达式超出指定的总长度时，返回的字符串为指定长度的两个**。
（8）REVERSE()函数
select REVERSE('basketball')
（9）LEN()函数
select LEN(' basketball ')
（10）CHARINDEX()函数
select CHARINDEX('a','basketball'),CHARINDEX('a','basketball',3),CHARINDEX('ba','basketball',5)
（11）SUBSTRING()函数
select SUBSTRING('basketball',1,6),SUBSTRING('basketball',LEN('basketball')/2,LEN('basketball'))
（12）LOWER()函数
select LOWER('BASKETBALL'),LOWER('Basketball')
（13）UPPER()函数
select UPPER('basketball'),UPPER('BAssketBall')
（14）REPLACE()函数
SELECT REPLACE('basketbaxx','x','l')
3、数学函数
数学函数主要用来处理数值数据，主要的数学函数有:绝对值函数，三角函数(包括正弦函数，余弦函数，正切函数，余切函数)、对数函数，随机函数等。在错误产生时，数学函数将返回空值null。
函数 | 说明 |
ABS() | 绝对值函数，ABS(x)返回x的绝对值 |
PI() | 返回圆周率的函数，PI()返回圆周率的值 |
SQRT() | 平方根函数，SQRT(x)返回非负数x的二次方根 |
RAND() | 获取随机函数的函数，RAND(x)返回一个随机浮点值v,范围在0~1之间(即0<=v<=1.0)。若指定一个整数参数x，则它被用作种子值，使用相同的种子数将产生重复序列。如果同一种子值多次调用RAND函数，它将返回同一生成值。 |
ROUND() | 四舍五入函数，ROUND(x,y)返回接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。 |
SIGN() | 符号函数，SIGN(x)返回参数的符号，x的值为负、零或正时，返回结果依次为-1、0或1。 |
CEILING()、FLOOR() | 获取整数的函数，CEILING(x)返回不小于x的最小整数值,FLOOR(x)返回不大于x的最大整数值； |
POWER()、SQUARE()、EXP() | 幂运算函数
|
LOG()、LOG10() |
|
RANDIANS()、DEGREES() |
|
SIN()、ASIN() |
|
COS()、ACOS() |
|
TAN()、ATAN()、COT() |
|
执行代码：
（1）绝对值ABS()函数和返回圆周率函数PI()
SELECT ABS(2),ABS(-3),PI() 
（2）平方根函数SQRT()
SELECT SQRT(2500),SQRT(60)
（3）获取随机函数RAND()
SELECT RAND(),RAND(),RAND(),RAND(2),RAND(2),RAND(5) 
当RAND（X）中参数X 值固定时，生成的随机数也保持一致。
（4）四舍五入函数ROUND()
SELECT ROUND(12.369,2),ROUND(-12.369,0),ROUND(12.369,-1),ROUND(12.369,-2) 
（5）符号函数SIGN()
SELECT SIGN(-4),SIGN(0),SIGN(4) 
（6）获取整数函数CEILING()、FLOOR()
SELECT CEILING(3.141592),FLOOR(3.141592)
（7）幂运算函数POWER()、SQUARE()、EXP()
SELECT POWER(4,3),SQUARE(3.33),EXP(3)
（8）对数函数LOG()、LOG10()
SELECT LOG(2),LOG(3),LOG10(1),LOG10(1000) 
（9）角度弧度转换函数RANDIANS()、DEGREES()
select RADIANS(90.0),RADIANS(180.0),DEGREES(PI()/2),DEGREES(PI())
（10）正弦函数，反正弦函数SIN()、ASIN()
select SIN(PI()/2),SIN(PI()),ASIN(1),ASIN(0)
（11）余弦函数，反余弦函数COS()、ACOS()
select COS(0),COS(PI()),ACOS(1),ACOS(0)
（12）正切函数、反正切函数、余切函数TAN()、ATAN()、COT()
select TAN(0.3),ROUND(TAN(PI()/2),0),ATAN(0.30933624960962325),ATAN(1),COT(0.3),1/TAN(0.3),COT(PI()/4)
4、数据类型转换函数
在同时处理不同数据类型的值时，SQL Server一般会自动进行隐士类型转换。对于数据类型相近的值是有效的，比如int和float， 但是对于其它数据类型，例如整型和字符类型，隐士转换就无法实现了，此时必须使用显示转换。为了实现这种显示转换，T-SQL提供了两个显示转换函数，分别是CAST和CONVERT函数，CAST(x AS type)和CONVERT(type,x)函数将一个类型的值转换为另一个类型的值。
代码如下：
select CAST('121231' AS DATE),CAST(100 AS CHAR(3)),CONVERT(TIME,'2012-05-01 12:11:10')
5、文本和图像转换函数
TEXTPTR函数
TEXTPTR(column)函数用于返回varbinary格式的text、ntext或者image字段的文本指针值。查找到的文本指针值可应用于readtext,writetext和updatetext语句。其中参数column是一个数据类型为text、ntext或者image的字段列。
示例如下：查询authors表中[name]字段十六字节文本指针；
create table authors(id int , [name] text)
insert into authors values(1 ,'this is a text')
select id , TEXTPTR([name]) from authors where id=1运行结果如下：
2. TEXTVALID函数
TEXTVALID('table.column',text_ptr)函数用于检查特定文本指针是否为有效的text、ntext或image函数。table.column为指定数据表和字段，text_ptr为要检查的文本指针。
示例如下：检查是否存在用于authors表中decription字段中的各个值的有效文本指针。
select id,'this is a text' = TEXTVALID(('authors.name'),TEXTPTR(name)) from authors运行结果如下：
说明：第一个1为id的值，第二个1表示查询的值存在。
6、系统函数
1.返回表中指定字段的长度
COL_LENGTH(table,column)函数返回表中指定字段的长度值。其返回值为int类型，table为要确定其列长度信息的表的名称，是nvarchar类型的表达式。column为要确定其长度的列的名称，是nvarchar类型的表达式。
数据准备:
create table student (
fid int primary key identity(1,1),
fname varchar(10))
insert into student values('Jamse')执行代码：
SELECT COL_LENGTH('student' , 'fname')运行结果如下：
2.返回表中指定字段的名称
COL_NAME(table_id,column_id)函数返回表中指定字段的名称。table_id是表的标识号，column_id是列的标识号，类型为int。
执行代码：
SELECT COL_NAME(OBJECT_ID('student') , 1)运行结果如下：
3.返回数据表达式的数据的实际长度函数
DATALENGTH(expression)函数返回数据表达式的数据的实际长度，即字节数。其返回值类型为int。null的长度为null。expression可以是任何数据类型的表达式。
执行代码：
SELECT DATALENGTH(fname) FROM student WHERE fid=1运行结果如下：
4.返回数据库的编号
DB_ID(database_name)函数返回数据库的编号，其返回值为SMALLINT类型，如果没有指定database_name,则返回当前数据库的编号。
执行代码：
SELECT DB_ID('LearnDb') , DB_ID()运行结果如下：
7、自定义函数：标量函数与表值函数
自定义函数分为二种，一种是标量值函数，另一种是表格值函数。
1.标量值函数：返回一个标量值
代码如下：
CREATE FUNCTION MySTR(@strs VARCHAR(50))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @str2 VARCHAR(30)SET @str2=@strs
RETURN @str2
END调用1：
SELECT dbo.MySTR('aa') AS result运行结果如下：
调用2：
DECLARE @str3 VARCHAR(30)
SET @str3=(select fname from student where fname=dbo.MySTR('Jamse') and fid=1 )
select @str3运行结果如下：
2.表格值函数
代码如下：
CREATE FUNCTION func_student(@fname VARCHAR(10))
RETURNS TABLE
AS
return(select fid , fname from student where fname like '%'+@fname+'%')执行代码：
SELECT * FROM func_student('J')运行结果如下：
代码2如下：
CREATE function func_student2 (@fname varchar(10))
Returns @student table(fid int , fname varchar(100))
As
Begin
Insert @student Select fid , fname from student WHERE fname LIKE '%'+@fname+'%'
return
End执行代码：
SELECT * FROM func_student2('J')运行结果如下：
扫一扫
8351
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
