SQL server常用函数(几乎工作上遇到的函数都在里面)

一、修改查询出来的字段属性

①、cast 和convert都是用来将一种数据类型的表达式转换为另一种数据类型的表达式

cast一般更容易使用,convert的优点是可以格式化日期和数值.

⑴、cast()语句

语句形式为：select cast(字段 as int) as 自定义字段名 from table1

查询table1的字段内容并且将数据类型转换为int类型显现出来，重新附一个别名

⑵、Convert() 语句

⑴语句形式为：select convert(int,字段) as 自定义字段名 from table2

查询table1的字段内容并且将数据类型转换为int类型显现出来，重新附一个别名

⑵语句形式为：select convert(char(10),getdate(),102)

获取当前日期，并且格式为yy.mm.dd(最多占10个字节)

第三参数如下：

日期类型 格式

100 03 19 2008 4:45PM

101 03/19/2008

102 2008.03.19

103 19/03/2008

104 19.03.2008

105 19-03-2008

106 19 03 2008

107 03 19, 2008

108 16:45:00

109 03 19 2008 4:45:00:

110 03-19-2008

111 2008/03/19

112 20080319

113 19 03 2008 16:45:00:

114 16:45:00:000

②、str()函数--数值转换字符类型

函数格式：str(参数1(必填)，参数2(选填)，参数3(选填))

参数1 数值字段，参数2 指定的总长度(包括逗号，小数，整数与空，默认为10)，参数3 保留的小数位数

转换规则：

先看整数部分是否满足转换长度，只要长度值小于整数长度就返回“*”;若长度值大于整数长度，再看小数部分。小数部分能按要求转换后仍不足转换长度，再在左侧补空格

二、对小数值进行取值

①、round() 函数--遵循四舍五入保留指定的小数位

函数格式：round(参数1，参数2)

参数1：数值。参数2：为小数位数 0代表个位，1为十分位，2为百分位...

如：select round(1.45,0); 输出：1

select round(1.55,0); 输出：2

②、floor() 函数--向下舍入为指定小数位数

函数格式：floor(‘数值’)

如：select floor(1.45); 输出：1

select floor(1.55); 输出：1

提示：不管有没有小数位都会舍弃小数取整数

③、ceiling() 函数--向上舍入为指定小数位数

函数格式：ceiling(‘数值’)

如：select ceiling(1.45); 输出；2

select ceiling(1.55); 输出；2

提示：只要有小数位就取整数位+1

三、数据替换

①、replace() 函数

函数格式：replace(参数1，参数2，参数3)

参数1：字段，参数2： 被替换的数据 参数3： 替换的数据

警告：参数1不能加引号！加引号后不是字段了而是常量值，它会将全部的值替换为这个常量，修改操作就会将全部的值修改为这个常量。切记提前做好数据备份！

Text数据类型的字段不能在replace函数中使用！

如1：update table set age=replace(age,20,18);

修改table表的age数据，将20岁都替换为18岁

如2：select replace(age,20,18) as age from table;

查询table表中的age，将查询出来的20替换为18岁

四、字符串操作函数

①、charindex()函数-返回索引位置

函数格式：charindex(参数1,参数2,参数3)

参数1：查找的数据，参数2：被查询的数据，参数3：从哪个索引位置开始查找

如：select charindex(‘猪头’,’你是大猪头他们都是大猪头’,1);

查询结果：4

select charindex(‘猪头’,’你是大猪头他们都是大猪头’,8);

查询结果：11

②、left()函数-返回截取左边的数据

函数格式：left(参数1,参数2)

参数1：数据，参数2： 截取范围

如：select left(‘我是大天才加油！’,4);

查询结果：我是大天

③、right()函数-返回截取右边的数据right()

函数格式：right(参数1,参数2)

参数1：数据，参数2：截取范围

如：select right(‘我是大天才加油！’,4)

查询结果：才加油！

④、DataLength()函数-返回字节长度

如：Select datalength(‘哈abc’)

查询结果：5

一般情况下一个汉字占两个字节
⑤、Len()函数-返回字符长度

如：select len(‘哈abc’)

查询结果：4

每个汉字和字母都占一个字符长度

⑥、concat()函数-拼接两个或多个字符串

函数格式：concat(str1,str2...)

如：select concat(‘我是’,’大天才’)

查询结果：我是大天才

⑦、Replicate()函数-返回一个重复的数据指定次数的字符串

函数格式：replicate(参数1，参数2)

如：select replicate(‘123’,3);

查询结果：123123123

⑧、reverse()函数-返回指定字符串的字符排序顺序颠倒的数据

函数格式：reverse(str)

参数(str) 可以是字符串、常数或一个列的值

如：select reverse(‘abcd’)

查询结果：dcba

五、排序函数

①、ROW_NUMBER() OVER() 排序函数

语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

解释：此函数是根据指定的字段就行分组，然后又根据另一个字段进行排序，得到一个排序的序号

例表studenty：

用法1：可以用于分组排序：

要求：根据name分组后按照score进行排序，需全部显现出来

Select *,row_number()over(partition by name order by score) as 排序 from studenty;

结果：

用法2：保留指定的前几位函数：

要求：只取每个name第一条数据

Select * from (select *,row_number()over(partition by name order by score) as排序from studenty) t where t.排序<=1;

如果想保留两条数据则为where t.排序<3;以此类推

结果：

②、ROW_NUMBER() OVER() 排序函数

语法：ROW_NUMBER() OVER( ORDER BY COLUMN)

解释：相比于上面一种，少一分组条件，直接根据字段进行排序然后得到一个排序的序号

六、判断或者检测自增(identity)的函数

①、判段一个表是否具有标识列

可以使用 objectproperty 函数确定一个表是否具有 identity（标识）列,用法:

Select objectproperty(object_id('表名'),'TableHasIdentity')

如果有，则返回1,否则返回0

②、判断某列是否是标识列

可使用 columnproperty 函数确定 某列是否具有identity属性,用法

Select columnproperty ( object_id('表名'),'列名','IsIdentity')

如果该列为标识列，则返回1,否则返回0

③、获取标识列的种子值

可使用函数ident_seed用法：

Select ident_seed ('表名')

④、获取标识列的递增量

可使用函数ident_incr用法：

select ident_inc('表名')

⑤、获取指定表中最后生成的标识值

可使用函数ident_current用法:

Select ident_current('表名')

注意事项：当包含标识列的表刚刚创建,为经过任何插入操作时，使用IDENT_CURRENT函数得到的值为标识列的种子值，

这一点在开发数据库应用程序的时候尤其应该注意。

七、大小写转换函数
①、LOWER(expr)函数

将expr英文全部转换为小写

如：select lower(‘NAME’); --输出：name
②、UPPER(expr)函数

将expr英文全部转换为大写

如：select upper(‘liu’); --输出：LIU

八、去空格函数

①、ltrim()函数-去除左边空格

select datalength(' g ') --输出：4

select datalength(rtrim(' g ')) --输出：2

②、Trtim()函数-去除右边空格

select datalength(' g ') --输出：4

select datalength(ltrim(' g ')) --输出：3

九、其他函数

①、isnull()函数

函数格式：ISNULL(Expression1，Expression2)

给定两个参数，如果Expression1是NULL，那么返回Expression2，否则返回Expression1。

如：select isnull(null,123); --输出：123

②、nullif()函数

函数格式：nullif(expression1, expression2)

NULLIF 函数在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1 的值

如：select nullif(‘我’,’我’); --输出：null