SQL Server 常用函数！！！
1、Ltrim、Rtrim去除空格函数： MS SQL Server中没有Trim函数，只有Ltrim（去除左侧空格）和Rtrim（去除右侧空格）函数
使用语法： Ltrim(字段) 、Rtrim(字段)
等效Trim方法（Oracle)：
使用举例：
select Ltrim(Rtrim(' Server课程 '))
返回：Server课程
2、Convert 函数 与 CAST 函数：用于SQL的数据类型转换，将一种数据类型的表达式转换为另一种数据类型的表达式
使用语法： Convert( 字段数据类型, 表字段或表达式)
使用举例：
TEST 字段为 int 类型，那么可用如下函数转为 varchar 的类型
SELECT convert(varchar,DATEPART(YEAR,GETDATE()))+'年'
返回：2020年
CAST( 表字段或表达式 AS 字段数据类型)
SELECT cast(DATEPART(YEAR,GETDATE()) as varchar)+'年'
返回：2020年
3.row_number（）函数：A.利用分组排序来自动产生行号，简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY 字段 DESC) 是先把【字段｝列降序，再为降序以后的每条xlh记录返回一个序号
row_number()over(partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）
*类似函数—— dense_RANK() over(order by 字段) dense_rank函数根据ORDER BY 排序后出现相同数据时，将排号相同，后面再继续生成序号接着连续的
*类似函数—— ntile(分组数值) over(order by 字段) ntile 函数是根据设置的分组数，来排序号，例如分组数值是2 ，则每两个为一组序号依次生成序号接着连续的
使用语法： row_number()over(partition by 字段 order by 字段)
使用举例：
create table employee (deptid int ,salary decimal(10,2),cood varchar(20));
insert into employee values(10,5500.00,'鞋');
insert into employee values(10,4500.00,'衣服');
insert into employee values(20,4500.00,'衣服');
insert into employee values(40,14500.00,'帽子');
insert into employee values(40,55500.00,'外套');
insert into employee values(50,7500.00,'裤子');
insert into employee values(150,7500.00,'裤子');
SELECT T.*,ROW_NUMBER() OVER(partition by salary ORDER BY salary desc) rum from employee t
B. Row_Number() OVER 去重详解
SELECT *
FROM (SELECT t.*,
ROW_NUMBER() OVER(PARTITION BY salary ORDER BY deptid DESC) ROW_NUM
FROM employee t
)a
WHERE ROW_NUM = 1;
如果这张表中有两条除deptid外完全一样的记录，而现在我们只需要一条，那么就可以用这种方法去重，通过ROW_NUMBER() OVER函数通过RECVTIME排序，下边令ROW_NUM = 1就可以找到一条记录了，这样可以去冗余。
5.CHARINDEX（）与 PATINDEX（）函数：寻找一个指定的字段在另一个字符串中的起始位置，都返回指定模式的开始位置。PATINDEX 可使用通配符，不能使用多内容组合，而 CHARINDEX 不可以使用通配符，可以多内容组合
使用语法：CHARINDEX（字段，字符串，起始位置）--起始位置不写默认为0
PATINDEX（字符串，字段）
使用举例：
select *from (
select =CHARINDEX(cood,'帽子'+'外套'+'裤子') as demo from employee emp
) t where t.demo>0
返回：1
3
5
5
*字段的内容依次从左到右查找在字符串组合 ‘'帽子外套裤子'’ 里面的起始位置
select *from (
select emp.*, patindex('%外%',cood) as demo from employee emp
) t where t.demo>0
返回：1
使用举例：
create table test(roon varchar(100))
insert into test
select '2栋6单元404室' union
select '3栋2单元2005室' union
select '1栋3单元103室' union
select '2栋3单元1806室' union
select '15栋2单元507室'
select * from test
输出：
*取不定长不同位置的字符串
select room_stand=substring(roon,charindex('元',roon)+1,charindex('室',roon)-charindex('元',roon)-1)
from test where roon like '%单元%室%'
输出：
对字符串进行复杂的处理：调用多个函数
【函数介绍】
- CHARINDEX(expressionToFind , expressionToSearch)：此函数会在第二个字符表达式中搜索一个字符表达式，这将返回第一个表达式（如果发现存在）的开始位置。需要注意的是：它返回的起始位置是从1开始的，而不是从0开始的。
可以使用函数CHARINDEX()确定指定符号的位置
SELECT CHARINDEX('-','AB-C') ;/*返回第一个符号“-”所在的位置*/
SELECT CHARINDEX('-','AB-CDEF-G',CHARINDEX('-','AB-CDEF-G')+1) ;/*返回第二个符号“-”所在的位置：从第一个符号往后找*/
按照指定符号截取文本，假设指定符号为“-”，截取字符串第一个符号前的所有内容。
SELECT SUBSTRING('AB-C',1,CHARINDEX('-','AB-C')) ;/*从第1位开始，取到第一个符号“-”个为止，包含符号“-”*/
SELECT SUBSTRING('AB-C',1,CHARINDEX('-','AB-C')-1) ;/*从第1位开始，取到第一个符号“-”个为止，不包含符号“-”*/
SELECT SUBSTRING('AB-CDEF-G',1,CHARINDEX('-','AB-CDEF-G',CHARINDEX('-','AB-CDEF-G')+1)-1) ;/*从第1位开始，取到第二个符号“-”个为止，不包含符号“-”*/
6.DATALENGTH（）与 LEN（）函数 ：DATALENGTH返回用来表示任何表达式的字节数，LEN返回传递给它的字符串长度 (一个中文字等于两个字符串，一个长度)
使用语法：DATALENGTH（字段或字符串）
LEN（字段或字符串）
使用举例：
SELECT DATALENGTH('SQL Server课程')
返回：14
select len('SQL Server课程')
返回：12
7.UPPER（）与LOWER（）函数：UPPER 把传递给它的字符串转换为大写，LOWER 把传递给它的字符串转换为小写
使用语法：UPPER（字段或字符串）
LOWER（字段或字符串）
使用举例：
select UPPER('server课程')
返回：SERVER课程
select LOWER('SERVER课程')
返回：server课程
8. LEFT（）与 RIGHT（）函数：LEFT 从字符串左边开始截取到指定数值位置的字符,RIGHT从字符串右边截取到指定数值位置的字符
使用语法：LEFT（字段或字符串，数值）
RIGHT（字段或字符串，数值）
使用举例：
SELECT LEFT('SQL SERVER课程',5)
返回：SQL S
SELECT RIGHT('SQL SERVER课程',5)
返回：VER课程
9. SUBSTRING（）函数：从字符串开始数值开始截取到结束数值的字符
使用语法：SUBSTRING（字段或字符串，开始数值，结束数值）
使用举例：
SELECT SUBSTRING('SERVER课程表',7,2)
返回：课程
10. REPLACE（）函数：查找字段或表达式里面存在的内容替换为另一个指定字符
使用语法：REPLACE（字段或字符串，存在字符，替换字符）
使用举例：
SELECT REPLACE('SQL Server课程','课程','数据库')
返回：SQL Server数据库
11. STUFF（）函数：在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串
使用语法：STUFF（字段或字符串，开始数值，结束数值，插入字符串）
使用举例：
SELECT STUFF('MySQL课程',6,3,'安装教程')
返回：MySQL安装教程
13. SPACE（）函数：返回由重复的空格组成的字符串
使用语法： SPACE（空格数值）
使用举例：
SELECT '下午'+SPACE(10)+'server课程'
返回：下午 server课程
14. REPLICATE（）函数：返回多次复制后的字符表达式
使用语法：REPLICATE（字段或字符串，复制次数数值）
使用举例：
SELECT REPLICATE(’+’,3)
返回：+++
15. STR（）函数：返回由数字数据转换来的字符数据
使用语法：STR（数字类型字段或者数值数据，转换字符串长度数值，保留小数位数数值） --两个数值参数不填写即默认四舍五入保留整数
*转换规则：a.先看整数部分是否满足转换长度，若转换长度值小于整数长度返回“**”,
b.若转换长度值大于整数长度（小数点算一位），再看小数点后位数长度补了多少位，此时[ 保留小数位数数值]起控制小数点位数作用
c.小数部分能按要求转换后仍不足转换长度，默认会在左侧补空格以达到转换长度*
使用举例：
SELECT STR(123.746381)
返回：124
a. SELECT STR(123.7487,2,3)
返回：**
b.SELECT STR(123.7487,6,1)
返回： 123.7
SELECT STR(123.74874565,8,3) --8 代表转换到 123.7487 位数，3代表 [ 保留小数位数数值] 可填写4以下的数值起是保留小数点后位数的作用，但因转换的长度为8位，返回内容只显示7位，则在前面有空格补了一位,如下 c. 执行结果可看出
返回： 123.749
c.select replace(STR(123.74874565,8,3),' ','0')
返回：0123.749
16. GetDate() 函数 :取得当前的系统的时间和日期
使用举例：
SELECT GETDATE()
返回：2020-11-25 10:33:51.800
select convert(varchar(4),GetDate(),120)+'0101' --varchar 参数的数值决定了截取日期时间的位数，120 是样式参数，详见CONVERT()函数；
返回：20200101
SELECT convert(varchar,DATEPART(YEAR,GETDATE()))+'年'+convert(varchar,DATEPART(MONTH,GETDATE()))+'月'+cast(DATEPART(DAY,GETDATE()) as varchar)+'日'
返回：2020年11月25日
17. DateDiff（） 函数 ：返回“开始日期”与“结束日期”之间的差值（如：年、天数等）。
返回值类型： DateDiff() 函数返回值类型为：Long（长整型），范围从 -2,147,483,648 到 2,147,483,647。
使用语法： DateDiff(参数 , 开始日期 , 结束日期)
参数取值：
|
说明 |
参数写法 |
参数简写 |
最大 |
|
年 |
Year
|
yy |
|
|
季度 |
Quarter |
q |
|
|
月 |
Month |
m |
|
|
周 |
Week |
wk |
|
|
日 |
Day |
d |
|
|
小时 |
Hour |
hh |
|
|
分钟 |
Minute |
mi |
|
|
秒 |
Second |
s |
68年 |
|
毫秒 |
Millisecond |
ms |
24天20小时31分23.648秒 |
使用举例：
SELECT DateDiff(ms,'2020-1-1 20:31:23','2020-1-2 20:31:23') --表示计算从'2020-1-1 20:31:23'的时间到'2020-1-2 20:31:23'总共过了多少毫秒
返回：86400000（毫秒）
SELECT DateDiff(DAY,'2020-11-1','2020-11-22')
返回：21（日）
SELECT convert(varchar(4),GetDate(),120)+'年已经过了'+convert(varchar,DateDiff(DAY,'2020-1-1',GetDate()))+'天'
返回：2020年已经过了329天
18. DateAdd（） 函数 ：用于在指定日期增加（或减去）参数设定的间隔。
语法： DateAdd(参数 , 加（减）间隔 , 指定日期) --加为正数，减为负数
参数取值：
|
说明 |
参数写法 |
参数简写 |
|
年 |
Year
|
yy |
|
季度 |
Quarter |
q |
|
月 |
Month |
m |
|
周 |
Week |
wk |
|
日 |
Day |
d |
|
小时 |
Hour |
hh |
|
分钟 |
Minute |
mi |
|
秒 |
Second |
s |
|
毫秒 |
Millisecond |
ms |
使用举例：
SELECT DateAdd(day,-5,'2020-01-10')
返回：2020-01-05 00:00:00.000
SELECT DateAdd(day,5,'2020-01-10')
返回：2020-01-15 00:00:00.000
select CONVERT(varchar(10),DateAdd(day,-5,'2020-01-10'),120)
返回：2020-01-05
19. Convert（） 函数 ：作为日期函数时，用来将日期转换为指定样式的新数据类型。
语法： Convert(数据类型(长度) , 需转换日期 , 样式参数)
常用参数取值：
|
样式参数 |
样式说明 |
长度 |
|
101 |
mm/dd/yyyy |
10 |
|
110 |
mm-dd-yyyy |
10 |
|
103 |
dd/mm/yyyy |
10 |
|
105 |
dd-mm-yyyy |
10 |
|
111 |
yyyy/mm/dd |
10 |
|
112 |
yyyymmdd |
8 |
|
108 |
hh:mm:ss |
8 |
|
114 |
hh:mm:ss:mmm |
12 |
|
120 |
yyyy-mm-dd hh:mm:ss |
19 |
|
121 |
yyyy-mm-dd hh:mm:ss.mmm |
23 |
使用举例：
SELECT Convert(VarChar(10),'2020-01-02 20:31:23',120)
返回：2020-01-02
SELECT Convert(VarChar(8),GetDate(),112)
返回:20200102
20. DatePart（） 函数 ：用来返回日期（时间）参数指定的部份，如年、月、日、时、分、秒等。
语法： DatePart(参数 , 日期)
参数取值：
|
说明
|
参数写法 |
参数简写 |
|
年 |
Year |
yy |
|
季度 |
Quarter |
q |
|
月 |
Month |
m |
|
周 |
Week |
wk |
|
日 |
Day |
d |
|
小时 |
Hour |
hh |
|
分钟 |
Minute |
mi |
|
秒 |
Second |
s |
|
毫秒 |
Millisecond |
ms |
使用举例：
SELECT '当前系统时间为'+cast(DatePart(HH,GetDate()) as varchar)+'时'+cast(DatePart(MI,GetDate()) as varchar)+'分'+cast(DatePart(S,GetDate()) as varchar)+'秒'
返回：当前系统时间为11时44分37秒
21. NULLIF（）函数：如果两个指定的字段或表达式相等，则返回空值；如果两个指定的字段或表达式不相等，则返回第一个字段或表达式的内容
使用语法: NULLIF（字段或表达式，字段或表达式）
使用举例：
（存储过程如下：）
ALTER PROCEDURE A
AS
BEGIN
DECLARE @I VARCHAR(50)
IF (SELECT NULLIF('字段1','字段2') AS PING ) IS NULL
BEGIN
SET @I='PING'
PRINT (@I+'字段1与字段2的数据相同')
END
ELSE
PRINT ('字段1与字段2的数据不相同')
END
（执行存储过程： ）
EXEC A；
返回：字段1与字段2的数据不相同
22. ISNULL（）函数：使用指定的替换值替换NULL空值
使用语法：ISNUL:（字段或表达式，替换的值）
使用举例：
SELECT ISNULL((NULLIF('SQL2','SQL2')),0)
返回：0
23.内部合计函数
1）COUNT（*） 返回行数
2）COUNT（DISTINCT 字段）返回指定列中唯一值的个数
3）SUM（字段）返回指定列或表达式的数值和；
4）SUM（DISTINCT 字段） 返回指定列中唯一值的和
5）AVG（字段）返回指定列或表达式中的数值平均值
6）AVG（DISTINCT 字段） 返回指定列中唯一值的平均值
7）MIN（字段）返回指定列或表达式中的数值最小值
8）MAX（字段）返回指定列或表达式中的数值最大值
9）RANGE（COLNAME） 返回指定列的最大值与最小值之差 = MAX（COLNAME）-MIN（COLNAME）
10）ABS(字段) 返回取绝对值
11）SIGN(字段) 返回对于正数返回+1，对于负数返回-1，对于0则返回0
