MSSQL各种函数使用

SubString()：用于截取指定字符串的方法。该方法有三个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定要截取的字符串的起始位置，起始值为 1 。
参数3：用于指定要截取的长度。
select substring(‘abcdef’,1,3) – 返回 abc

Left()：用于返回指定字符串中指定长度的左侧部分。该方法有两个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定要返回的子字符串的长度。
select LEFT(‘abc123’,3) – 返回 abc
select LEFT(‘左侧部分右侧部分’,4) – 返回 左侧部分
Right()：用于返回指定字符串中指定长度的右侧部分。该方法有两个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定要返回的子字符串的长度。
select RIGHT(‘abc123’,3) – 返回 123
select RIGHT(‘左侧部分右侧部分’,4) – 返回 右侧部分
CharIndex()：用于返回指定字符串中指定子字符串出现的起始位置。如果未找到就返回 0。该方法有两个参数：
参数1：用于指定要进行查找的字符串。
参数2：用于指定用作检索的字符串。
select charindex(‘a’,‘123a123’) – 返回 4
select charindex(‘abc’,‘123a123’) – 返回 0
select charindex(‘abc’,‘123abc123’) – 返回 4
Patindex()：返回指定字符串表达式中指定模式第一次出现的起始位置，未找到则返回0。该方法有两个参数：
参数1：指定要匹配的字符串及匹配模式。
参数2：指定要被匹配的字符串表达式。

select Patindex(‘abc’,‘123abc456abc789’) – 返回 0
select Patindex(‘abc’,‘abc’) – 返回 1
select Patindex(‘abc%’,‘123abc456abc789’) – 返回 0
select Patindex(’%abc%’,‘123abc456abc789’) – 返回 4
select Patindex(’%[abc]%’,‘123abc456abc789’) – 匹配字符串 abc 第一次出现的位置，返回 4
select Patindex(’%[^abc]%’,‘123abc456abc789’) – 匹配不等于字符 a、b、c 的其他字符第一次出现的位置，返回 1
select Patindex(’%[^abc]%’,‘b123abc456abc789’) – 匹配不等于字符 a、b、c 的其他字符第一次出现的位置，返回 2

Stuff()：用于删除指定长度的字符，并在删除的位置插入新的字符/值。该方法有四个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定要删除字符的起始位置。
参数3：用于指定要删除字符的长度。
参数4：用于指定在删除的位置插入的新的字符串/值。
select stuff(‘123abc456’,4,3,‘ABC’) – 返回 123ABC456
select stuff(‘123abc456’,1,3,’’) – 返回 abc456，用空字符串替代
示例1：WITH tb AS(
SELECT RJZ.RJZID FROM cnyw_cn_rjz2019 RJZ LEFT JOIN cnyw_gs_szjl2019 SZJL ON RJZ.YHLSH=SZJL.zflsh
WHERE RJZ.RJZID IN(‘DFA8E7E4-40F8-403B-9BF1-B147E4903BA5’,‘49F82D05-1D35-4FA5-8EAD-C178AD4180C1’) AND SZJL.gsnm IS NULL )
select stuff((select ‘,’+RJZID from tb for xml path(’’)),1,1,’’)
示例2：

select branchunitid, stuff
(
(
select ‘,’ +[funcareaid] from DIPDEPLOYUNITINFO t where t.branchunitid = a.branchunitid for xml path(’’)

   ),
1, 
1, 
''

) AS ZNY
from DIPDEPLOYUNITINFO a
group by branchunitid

Len()：用于返回指定文本的值的长度。前导空格计算在内，尾随空格不计算在内。该方法有一个参数：
参数1：用于指定要操作的文本或字符串。
select len(‘123’) – 返回 3
select len(‘字符串’) – 返回 3
类比datalength():返回字符数
SELECT len(‘32我’) --返回3
SELECT datalength(‘32我’) --返回4

Lower()：用于返回指定英文字符串的小写形式的字符串。如果不为英文字符串，则返回原值。该方法有一个参数：
参数1：用于指定要转换为小写形式的字符串。
select lower(‘ABC’) – 返回 abc
select lower(‘123’) – 返回 123
Upper()：用于返回指定英文字符串的大写形式的字符串。如果不为英文字符串，则返回原值。该方法有一个参数：
参数1：用于指定要转换为大写形式的字符串。
select upper(‘abc’) – 返回 ABC
select upper(‘123’) – 返回 123
Ltrim()：用于返回删除前导空格之后的字符串。该方法有一个参数：
参数1：用于指定要进行删除前导空格操作的字符串。

select ltrim(’ 123’) – 返回 123
select ltrim(’ 好多空格’) – 返回 好多空格
select len(’ 123’) – 返回 11
select len(ltrim(’ 123’)) – 返回 3

Rtrim()：用于返回截断尾随空格之后的字符串。该方法有一个参数：
参数1：用于指定要进行截断尾随空格操作的字符串。
select rtrim('123 ') – 返回 123
select len(rtrim(‘123 ‘)) – 返回 3
Replace()：用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。该方法有三个参数：
参数1：用于指定要操作的字符串，即被匹配的字符串。
参数2：用于指定要进行匹配的字符串。
参数3：用于指定用作替换存在的匹配项的字符串。
–把 abc 替换为 xxx
select replace(‘123abc456’,‘abc’,‘xxx’) – 返回 123xxx456
– 用空字符串替换匹配项
select replace(‘123abc456’,‘abc’,’’) – 返回 123456

Str()：返回一个指定数值的等效字符串。该方法有三个参数：
参数1：指定要操作的数值。
参数2：指定要返回字符串的长度。默认值为10，即不指定默认长度为10，不足用空字符填充。
参数3：要返回的小数位数。默认值为0，即不指定就不保留小数，不足指定位数，用0补充。

select str(123.10) – 返回 123
select len(str(123.10)) – 返回 10
select str(123.10,5) – 返回 123
select len(str(123.10,5)) – 返回 5
select str(123.10,20,5) – 返回 123.10000
select len(str(123.10,20,5)) – 返回 20

Space()：用于返回指定数值长度的空格字符串。该方法有一个参数：
参数1：指定返回空格字符串的长度。
select ‘1’+space(3)+‘2’ – 返回 1 2
select len(‘1’+space(3)+‘2’) – 返回 5
Reverse()：用于对指定字符串进行反转，按照单个字符进行反转。该方法有一个参数：
参数1：指定需要执行反转操作的字符串。
select reverse(‘123’) – 返回 321
select reverse(‘abc’) – 返回 cba
Replicate()：用于返回一个对指定字符串重复指定次数后的字符串。该方法有两个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定字符串重复的次数。如果为0，返回空字符串；如果为负数，则返回 null。
select replicate(‘123’,3) – 返回 123123123
select replicate(‘a’,3) – 返回 aaa
Quotename()：返回一个 Unicode 字符串，根据指定分隔符，返回一个有效的字符串标识符。该方法有两个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定分隔符，可以是单引号（’）、左括号（[）、右括号（]）、左右括号（[]）或双引号（"）。如果未指定，默认使用左右括号。

select quotename(‘123’,’[]’) – 返回 [123]
select quotename(‘123’,’’’’) – 返回 ‘123’
select quotename(‘123’,’"’) – 返回 “123”
select quotename(‘12[]3’,’[]’) – 返回 [12[]]3],右括号加倍表示转义字符。

Parsename()：返回对象名称的指定部分。 可检索的对象部分包括对象名称、所有者名称、数据库名称和服务器名称。其实也可以用于截取字符串。该方法有两个参数：
参数1：要检索其指定部分的对象的名称。此名称可包含四部分：服务器名称、数据库名称、所有者名称以及对象名称。
参数2：要返回的对象部分。只能为1至4的数值。1 = 对象名称；2 = 架构名称；3 = 数据库名称；4 = 服务器名称
select parsename(‘server.dbo.dbTest.table’,1) tablename,
parsename(‘server.dbo.dbTest.table’,2) dbname,
parsename(‘server.dbo.dbTest.table’,3) schemaname,
parsename(‘server.dbo.dbTest.table’,4) servername

这种截取方式类似于字符串分割，不过这里是倒着截取的，所以在一定程度上可以用于字符串的截取。不过只支持小数点（.）的分割，而且只支持四位。
select parsename(‘192.168.1.1’,4) col1,
parsename(‘192.168.1.1’,3) col2,
parsename(‘192.168.1.1’,2) col3,
parsename(‘192.168.1.1’,1) col4

Getdate()：获取当前日期时间。
select getdate()

Year()：获取指定日期表达式的年。该方法有一个参数：
参数1：指定要操作的日期表达式。
select year(‘2016-01-01’) – 返回 2016
select year(‘2017-01-01 12:30:30’) – 返回 2017
select year(getdate()) – 返回 2017
Month()：获取指定日期表达式的月。该方法有一个参数：
参数1：指定要操作的日期表达式。
select month(‘2016-01-01’) – 返回 1
select month(‘2017-10-01 12:30:30’) – 返回 10
select month(getdate()) – 返回 5
Day()：获取指定日期表达式的日。该方法有一个参数：
参数1：指定要操作的日期表达式。
select day(‘2016-01-01’) – 返回 1
select day(‘2017-10-03 12:30:30’) – 返回 3
select day(getdate()) – 返回 11
Isdate()：用于判断指定的字符串表达式是否为日期。返回 1，表示指定字符串表达式是日期。返回 0，表示指定字符串表达式不是日期。该方法有一个参数：
参数1：用于指定要操作的字符串表达式。

select isdate(‘10/30/2017’) – 月/日/年 返回 1
select isdate(‘30/10/2017’) – 日/月/年 返回 0
select isdate(‘2017/10/30’) – 年/月/日 返回 1
select isdate(‘02/29/2017’) --用于判断闰年,由于2017不是闰年,所以没有29 返回 0
select isdate(‘2017-01-01’) – 返回 1
select isdate(‘2017-01-01 10:10:10’) – 返回 1
select isdate(‘01-01-2017’) – 返回 1
select isdate(‘01-01-2017 10:10:10’) – 返回 1

Datename()：返回指定日期的指定日期的部分的字符串。该方法有两个参数：
参数1：指定返回指定日期的指定部分的字符串。可以为下图中的日期部分或者缩写。
参数2：指定要操作的日期字符串。

select datename(year,‘2017-01-01 10:10:10’) – 返回 2017
select datename(yyyy,‘2017-01-01 10:10:10’) – 返回 2017
select datename(month,‘2017-01-01 10:10:10’) – 返回 01
select datename(mm,‘2017-01-01 10:10:10’) – 返回 01

select datename(quarter,‘2017-05-02 10:10:10’) – 表示一年的第几个季度 返回 2
select datename(weekday,‘2017-02-02 10:10:10’) – 表示一周的星期几 返回 星期四
select datename(week,‘2017-02-02 10:10:10’) – 表示一年的第几周 返回 5
select datename(dayofyear,‘2017-02-02 10:10:10’) – 表示一年的第几天 返回 33
select datename(hh,‘2017-01-01 10:30:25’) – 返回 10

Datepart()：返回指定日期的指定日期的部分的整数。Datepart() 方法和 Datename() 的方法使用方式类似，只不过 Datename() 方法返回的是一个字符串，而 Datepart() 方法返回的是一个整数数值。该方法有两个参数：
参数1：指定返回指定日期的指定部分的字符串。可以为上图中的日期部分或者缩写。
参数2：指定要操作的日期字符串。

select datepart(year,‘2017-01-01 10:10:10’) – 返回 2017
select datepart(yyyy,‘2017-01-01 10:10:10’) – 返回 2017
select datepart(month,‘2017-01-01 10:10:10’) – 返回 1
select datepart(mm,‘2017-01-01 10:10:10’) – 返回 1

select datepart(quarter,‘2017-05-02 10:10:10’) – 表示一年的第几个季度 返回 2
select datepart(weekday,‘2017-02-02 10:10:10’) – 表示一周的星期几,即一周的第几天,星期日为一周的第一天 返回 5
select datepart(week,‘2017-02-02 10:10:10’) – 表示一年的第几周 返回 5
select datepart(dayofyear,‘2017-02-02 10:10:10’) – 表示一年的第几天 返回 33
select datepart(hh,‘2017-01-01 10:30:25’) – 返回 10

Coalesce()：返回所有参数中的第一个非空（not null）表达式。该方法最多可以有 n 个参数，但是最少要有两个参数。
SELECT COALESCE(‘abc’,’’) – 返回 abc
SELECT COALESCE(NULL,’’) – 返回 空字符串
SELECT COALESCE(null,null,‘123’,‘abc’) – 返回 123
PS：这个函数是返回第一个非空的值，所以参数里面必须最少有一个非空的值。

ISNull()：判断指定的表达式一是否为空（null），如果为空则返回表达式二的值，否则返回表达式一的值，类似于C#中的三元运算符。该方法有两个参数：
参数1：指定要操作的表达式一。
参数2：指定要操作的表达式二。
select isnull(’’,‘123’) – 返回 空字符串
select isnull(null,‘123’) – 返回 123
select isnull(‘123’,‘abc’) – 返回 123
select isnull(null,null) – 返回 null

Convert():函数是把日期转换为新数据类型的通用函数
Style ID Style 格式
111 yy/mm/dd
112 yymmdd
120 或者 20 yyyy-mm-dd hh:mi:ss(24h)
121 或者 21 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-ddThh:mm:ss.mmm（没有空格）

CONVERT(VARCHAR(8),GETDATE(),112) 20190102

CONVERT(VARCHAR(36),GETDATE(),21)