SQL SERVER项目脚本中现在遇到过得函数（整理）

通过用过的在网上进行查找相关的解释与说法，进行更深刻的理解，在此进行整理

（1.CHARINDEX ，2.LEFT （RIGHT）,3.SUBSTRING ，4.LEN，5.CAST ，

6.CONVERT，7.STUFF，8.DATEADD，9.DAY（YEAR；MONTH），10.DATEDIFF ，

11.celling（floor），12.REPLICATE，13.LTRIM（RTRIM；TRIM），14.STR，15.DATEPART

16.ABS ，17.REPLACE ，18.ROUND，19.Lower（UPPER））

1.CHARINDEX 用于在SQL语句中进行判断一个字符串中是否包含另一个字符串，找到字符（char）的位置（index）

      通过CHARINDEX如果能够找到对应的字符串，则返回该字符串位置，否则返回0。

基本语法如下：

　　CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

      expressionToFind ：目标字符串，就是想要找到的字符串，最大长度为8000 。

　　expressionToSearch ：用于被查找的字符串。

      start_location：开始查找的位置，为空时默认从第一位开始查找。

参考地址：https://www.cnblogs.com/qianxingdewoniu/p/6858580.html

2.LEFT返回字符串中从左边开始指定个数字符。(RIGTH：返回字符串从右边开始指定个数字符。)

基本语法如下：

LEFT (character_expression， integer_expression)  （RIGHT(character_expression， integer_expression)）

character_expression：用于被指定的字符串

integer_expression：指定的需要返回的个数

3.SUBSTRING返回字符串根据指定位置的个数字符

基本语法如下：

SUBSTRING ( expression, start, length )

expression：被指定的字符串（字符串、二进制字符串、文本、图像、列或包含列的表达式。请勿使用包含聚合函数的表达式。）

start：整数或可以隐式转换为 int 的表达式，指定子字符串的开始位置。

length ：整数或可以隐式转换为 int 的表达式，指定子字符串的长度。

4.LEN返回文本字段中值的长度

基本语法如下：

LEN(column_name)

column_name：需要获取长度的值

有固定规律的连接数据，获取其中数据有多少个数据连接

5.CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型

基本语法如下：

CAST (expression AS data_type)

expression ：需要转换的数据

data_type：转换成的类型

6.CONVERT 类型转换，比较常用的是对日期转换格式

基本语法如下：

CONVERT(data_type(length),data_to_be_converted,style)

data_type(length)：规定目标数据类型（带有可选的长度）

data_to_be_converted：含有需要转换的值

style：规定日期/时间的输出格式

7.STUFF删除指定长度的字符并在指定的起始点插入另一组字符。

基本语法如下：

STUFF ( character_expression1 , start , length ,character_expression2 ) 

character_expression1：在此字符数据上进行操作 ，由字符数据组成的表达式。character_expression 可以是常量、变量，也可以是字符或二进制数据的列。

start：是一个整形值，指定删除和插入的开始位置。如果 start 或 length 是负数，则返回空字符串。如果 start 比第一个 character_expression 长，则返回空字符串。

length：是一个整数，指定要删除的字符数。如果 length 比第一个 character_expression 长，则最多删除到最后一个 character_expression 中的最后一个字符。

character_expression 2：插入的数据字段

8.DATEADD函数在日期中添加或减去指定的时间间隔。

基本语法如下：

DATEADD(datepart,number,date)

datepart：以下值都可以

年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
纳秒	ns

number：添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。

date：合法的日期表达式

9.DAY函数返回指定日期的日的部分（YEAR函数返回指定日期的年的部分；MONTH函数返回指定日期的月的部分）

基本语法如下：

DAY(date) （YEAR(date) ；MONTH(date) ）

date：参数是合法的日期表达式

10.DATEDIFF函数返回两个日期之间的时间。

基本语法如下：

DATEDIFF(datepart,startdate,enddate)

datepart：以下值都可以

年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
纳秒	ns

startdate：合法的日期表达式。

enddate：合法的日期表达式。

11.ceiling函数返回大于或等于所给数字表达式的最小整数。（floor函数返回小于或等于所给数字表达式的最大整数。）

基本语法如下：

celling(data)

data：精确数字或近似数字数据类型类别的表达式（bit数据类型除外）。其返回值的数据类型为与data相同的类型。

12.REPLICATE函数按指定的次数重复字符串

基本语法如下：

REPLICATE(input_string, count)

input_string：是一个表达式，其值为字符或二进制类型的值。

count：重复的次数

13.LTRIM函数是指去掉左边的空格（将所有字串起头的空白移除）（RTRIM函数是指去掉右边的空格（将所有字串结尾的空白移除）；TRIM函数从字符串的两端删除空格或指定的字符。）

基本语法如下：

LTRIM(string)  RTRIM(string) 

string：被操作的字符串

TRIM([ [位置] [要移除的字串] FROM ] 字串)  （网上查了一下，属于SQL Server 2017新增的函数）

[位置] 的可能值为 LEADING (起头), TRAILING (结尾), or BOTH (起头及结尾)。 这个函数将把 [要移除的字串] 从字串的起头、结尾，或是起头及结尾移除。如果我们没有列出 [要移除的字串] 是什么的话，那空白就会被移除。

没有最新的数据库版本，SO 网上找的例子：

此示例使用TRIM()函数从字符串：'$$$Hello..'中删除字符:.和$：

SELECT      TRIM('.$' FROM '$$$Hello..') result 

执行上面查询语句中，得到以下结果：

result ---------- Hello  (1 row affected) 

14.STR函数主要用于返回与指定数值表达式对应的字符(只能是数字)

基本语法如下：

STR(float_expression [ , length [ , decimal ] ])

float_expression ：是一个可带有小数点的数字数据类型的表达式【必填】

length ：表示转换为字符数据的总长度，它包括小数点、符号、数字以及空格，默认长度值为10【可无】

decimal ：指定小数点后的位数，decimal必须小于或等于16，如果大于16，则会截断结果，使其保持小数点后有16位【可无】

15.DATEPART函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

基本语法如下：

DATEPART(datepart,date)

datepart：参数可以是下列的值

datepart	缩写
年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
纳秒	ns

date：合法的日期表达式

16.ABS对数据进行绝对值操作

基本语法如下：

ABS ( numeric_expression )

numeric_expression ：精确数字或近似数bai字数据类型du类zhi别的表达式，或者可隐式转dao换为 float 数据类型的表达式。

17.REPLACE（替换）用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。

基本语法如下：

REPLACE ( ''string_replace1'' , ''string_replace2'' , ''string_replace3'' )

''string_replace1'' ：待搜索的字符串表达式。string_replace1 可以是字符数据或二进制数据。

''string_replace2''：待查找的字符串表达式。string_replace2 可以是字符数据或二进制数据。

''string_replace3''：替换用的字符串表达式。string_replace3 可以是字符数据或二进制数据。

18.ROUND函数用于把数值字段舍入为指定的小数位数。

基本语法如下：

 ROUND(column_name,decimals[,p3])

column_name： 必需。要舍入的字段。

decimals：必需。规定要返回的小数位数。

p3：选填。如果为0或不输入，则表示进column_name进入四舍五入，如ROUND(123.86,1) =123.90 如果P3是不为0的数,则对column_name进行截断,可以理解为不四舍五入 ROUND(123.86,1,1)=123.80

19.Lower将大写字符数据转换为小写字符数据（UPPER将小写字符数据转换为大写字符数据后返回字符表达式）

基本语法如下：

Lower(character_expression)  UPPER(character_expression)

character_expression：是指定要进行转换的字符串

SELECT LEFT('测试01`测试002`6`4', 3)
SELECT RIGHT('测试01`测试002`6`4', 3)
SELECT LEFT('ABCDFEVDF', 1)
SELECT RIGHT('ABCDFEVDF', 1)
SELECT LEFT('ABCDFEVDF', 2)
SELECT RIGHT('ABCDFEVDF', 2)

SELECT CHARINDEX('`', '测试01`测试002`6`4')
SELECT CHARINDEX('`', '01`测试002`6`4')
SELECT CHARINDEX('`', '3`测试002`6`4')
SELECT CHARINDEX('`', '3`测试002`6`4',5)
SELECT CHARINDEX('cd', 'abcdsecfcda')

SELECT SUBSTRING('测试001ceshi002',2,3)  
SELECT SUBSTRING('测试001ceshi002',4,8)  
SELECT SUBSTRING('测试001ceshi002',5,30)  

SELECT LEN('测试a001ceshi嗯嗯')
--如果字符串中包含前导空格和尾随空格，则函数将它们包含在内（如果数据完之后最后空格则不包含在内）
SELECT LEN(' CESHI 测试')
SELECT LEN(' CESHI 测试  ')
--LEN函数在对待英文字符和汉字字符时，返回的字符串长度是相同的，一个汉字也算作一个字符
SELECT LEN('ceshi')
SELECT LEN('测试')
--LEN函数在处理纯数字也将其当作字符串，但是纯数字可以不使用引号
SELECT LEN('123`45')

SELECT CAST('9.0' AS decimal)
SELECT CAST('9.0' AS decimal(18,2))
SELECT CAST('2020' AS datetime)
SELECT CAST('2020-03-03' AS datetime)
SELECT CAST('2020/02/02' AS datetime)
SELECT CAST('2020/02/02' AS nvarchar(50))

select LEN('a,b,c,d,e,f,g')
select len(replace('a,b,c,d,e,f,g',',',''))
select LEN('a,b,c,d,e,f,g')-len(replace('a,b,c,d,e,f,g',',',''))+1

SELECT CONVERT (decimal(18,2),'123456')
SELECT CONVERT (nvarchar(4),'123456')

SELECT CONVERT(nvarchar(19),GETDATE()),CONVERT(nvarchar(10),GETDATE(),120),CONVERT(nvarchar(50),GETDATE(),120),
CONVERT(nvarchar(11),GETDATE(),111),CONVERT(nvarchar(24),GETDATE(),112),
CONVERT(nvarchar(110),GETDATE(),109)



SELECT STUFF('abcdef', 2, 3, 'ijklmn') --删除的bcd
SELECT STUFF('测试001',2,2,'') --删除‘试0’ 
SELECT STUFF('测试001',10,1,'')--start超出字符串
SELECT STUFF('测试001',-2,2,'')--start为负数



--日：在当前日期上加两天
select DATEADD(day,2,GETDATE()), DATEADD(dd,2,GETDATE()) --2020-08-13 16:45:14.877

--月：在当前日期上加两个月
select DATEADD(mm,2,GETDATE()),DATEADD(MONTH,2,GETDATE()) -- 结果：2020-10-11 16:45:14.877

--年：在当前日期上加两年
select DATEADD(yy,2,GETDATE()) , DATEADD(year,2,GETDATE())  --结果：2022-08-11 16:45:14.877


select DAY('2020-06-20') --日
select YEAR('2020-06-20') --年
select MONTH('2020-06-20') --月


select DATEDIFF(DD,'2020-06-15','2020-06-18') --日 结果 3
select DATEDIFF(DD,'2020-06-25','2020-06-18') --结果 -7
select DATEDIFF(YY,'2020-06-25','2020-06-18')--年 结果 0
select DATEDIFF(YY,'2020-06-25','2021-06-18')--结果 1
select DATEDIFF(YY,'2022-06-25','2021-06-18')--结果 -1
select DATEDIFF(MM,'2020-06-25','2020-03-18')--月 结果 -3
select DATEDIFF(MM,'2020-02-25','2020-03-18')--结果 1


select CEILING('12.1')
select floor ('12.8')

select REPLICATE('测试',3)
select REPLICATE('123',1)

select LTRIM(' ceshi ') --结果：'ceshi '
select RTRIM(' ceshi ')--结果：' ceshi'


select STR('123')--结果：‘       123’(len(STR('123'))=10默认长度为10)
select str('123456789')--结果：‘ 123456789’
select str('1234567890') --结果：‘1234567890’
select str('123',3)--结果：‘123’
select str('123',2)--结果：‘**’ 长度不够
select str('1234567890',3)--结果：‘***’ 长度不够
select str('123',4)--结果：‘ 123’
select str('123.6',4)--结果：‘ 124’
select str('123.3',4)--结果：‘ 123’
select str('123.3',6)--结果：‘   123’
select str('123.1',4,3)--结果：‘ 123’
select str('123.1',5,3)--结果：‘123.1’
select str('123.1',6,3)--结果：‘123.10’
select str('123.1',8,3)--结果：‘ 123.100’
--字段长度超出10的情况
select str('123456789010')--结果：‘**********’
select str('123456789010',15)--结果：‘   123456789010’
select str('123456789010',15,3)--结果：‘123456789010.00’

select datepart(YY,'2020-06-18')--结果："2020"
select datepart(MM,'2020-06-18')--结果："6"
select datepart(DD,'2020-06-18')--结果："18"

select ABS('-1')--结果："1"
select ABS(-1)--结果："1"
select ABS(-1.026)--结果："1.026"
select ABS(-1.6)--结果："1.6"
select ABS(1.6)--结果："1.6"

select REPLACE('abcdefgabcdefcd','cd','99')--结果："ab99efgab99ef99"
select REPLACE(1255,5,3)--结果："1233"
select REPLACE('1255',5,3)--结果："1233"
select REPLACE(1255,'5',3)--结果："1233"
select REPLACE(1255,5,'3')--结果："1233"

SELECT ROUND(1.21,1)--结果："1.20"
SELECT ROUND(1.21,2)--结果："1.21"
SELECT ROUND(1.2,2)--结果："1.2"
SELECT ROUND(1.2116,3)--结果："1.2120"
SELECT ROUND(1.2116,3,1)--结果："1.2110"

select LOWER('ABHUes')--结果："abhues"
select UPPER('ABHUes')--结果："ABHUES"