Sql server函数的学习2（游标函数、日期函数、字符串操纵函数）

一、游标函数与变量

　　游标可以处理多行数据，在过程循环中一次访问一行。和基于集合的高效操作相比，这个功能对系统资源的消耗更大。

可以用一个函数和两个全局变量来管理游标操作

1、CURSOR_STATUS() 函数

　　cursor_status()函数返回一个整型值，表示传递给这个函数的游标类型遍历的状态。有很多不同类型

游标会影响这个函数的操作。

函数常见的返工值

2、@@CURSOR_ROWS 全局变量

　　@@cursor_rows变量是一个整型值，表示在当前连接中打开的游标中的行数。根据游标类型，这个值也能不

代表结果集中的实际行数。

3、@@FETCH_STATUS 全局变量

　　@@fetch_status 变量是一个标记，用于表示当前游标指针的状态。这个变量主要用来判断某行是否存在，以及

在执行了fecthc next 语句后，是否已执行到结果集的尾部。打开游标时，@@fetch_status变量值为-1。 一旦把第

一个值放在游标中，@@fetch_status变量值就变成0.当不再把更多行放在游标中时，该变量的值将变回-1。

游标例子：

---游标循环遍历（不带事务）--
begin
    declare @a int,@error int    
    declare @temp varchar(50)
    set @a=1
    --申明游标为Uid
    declare order_cursor cursor 
    for (select [Uid] from Student)
    --打开游标--
    open order_cursor
    --开始循环游标变量--
    fetch next from order_cursor into @temp
    while @@FETCH_STATUS = 0    --返回被 FETCH语句执行的最后游标的状态--
        begin            
            update Student set Age=20+@a,demo=@a where Uid=@temp
            set @a=@a+1
            fetch next from order_cursor into @temp   --转到下一个游标
        end        
    close order_cursor  --关闭游标
    deallocate order_cursor   --释放游标
end
go

二、日期函数

　　日期函数可以操作datetime 与smalldatetime类型的值。有些函数可用于解析日期值

的日期与实际部分，有些函数可用于比较、操纵日期、时间值。

日期数据类型的区别如下表：

1、DATEADD()函数

　　DATEADD()函数用于日期/时间值上加上日期单位间隔。该函数返回值的类型是datetime类型

比如，要得到2017年4月29日起30天后的日期，可以使用一下语句

select DATEADD(DAY,30,'2017-04-29')

查询结果

2017-05-29 00:00:00.000

1）DATEADD()函数的时间间隔参数

2）DATEADD()函数的使用

--18年后的日期--
select DATEADD(YEAR,18,'2000-03-12')
--返回：2018-03-12 00:00:00.000

--3年前的日期--
select DATEADD(YEAR,-3,'2000-03-12')
--返回：1997-03-12 00:00:00.000

--2个月后日期--
select DATEADD(MONTH,2,'2000-03-12')
--返回：2000-05-12 00:00:00.000

--3天后日期--
select DATEADD(DAY,3,'2000-03-12')
--返回：2000-03-15 00:00:00.000

--65秒后日期--
select DATEADD(SECOND,65,'2000-03-12')
--返回：2000-03-12 00:01:05.000

--CONVERT()和DATEADD()组合使用--
select CONVERT(varchar(20),DATEADD(Day,12,'2000-03-12'),120)
--返回：2000-03-24 00:00:00

2、DATEDIFF()函数

　　DATEADD()和DATEDIFF()函数可以看作一对表兄弟，有点像乘法与除法。在等式的两端有四个元素：起始日期、时间间隔（datepart）、

差值和最终日期。如果DATEADD(）函数中使用起始日期、一个整型值和一个时间间隔，就可返工最终日期值。如果提供了起始日期、时间间隔、

最终日期，DATEDIFF()函数就可以返回差值。

1）DATEDIFF()函数简单使用

------DATEDIFF()函数使用--------

--两个日期间隔的年数--
select DATEDIFF(YEAR,'2008-08-12','2016-08-12')
--返回：8

--两个日期间隔的月数--
select DATEDIFF(MONTH,'2008-08-12','2016-08-12')
--返回：96

--两个日期间隔的天数--
select DATEDIFF(DAY,'2008-08-12','2016-08-12')
--返回：2922

3、DATAPARY() 与DATENAME() 函数

　　这两个函数用于返回datetime或shortdatetime值得日期部分。DatePart()函数返回一个整型值，DateName()返回一个字符串。

1）DATAPARY() 与DATENAME() 函数使用

------DATEPART()和DATANAME()函数使用--------

--返回指定日期的月份--
select DATEPART(MONTH,'2008-04-05')
--返回：4

--返回指定日期的天数--
select DATEPART(DAY,'2008-04-05')
--返回：5

--返回指定日期的小时--
select DATEPART(HOUR,'2008-04-05 13:23:45')
--返回：13

--返回指定日期的月份--
select DATENAME(MONTH,'2008-04-05')
--返回：04

--返回指定日期的天数--
select DATENAME(DAY,'2008-04-05')
--返回：05

--返回指定日期的小时--
select DATENAME(HOUR,'2008-04-05 13:23:45')
--返回：13

4、GETDATE() 与 GETUTCDATE() 函数

　　这两个函数都用于返回datetime类型的当前日期与时间。GETUTCDATE()函数使用服务器上的时区设置来求出UTC时间，

这和格林威治标准时间或飞行员所说的"祖鲁时"(Zulu Time)是一样的。两个函数都能精确到3.33毫秒

1）GETDATE() 与 GETUTCDATE() 函数使用

-----GETDATE()和GETUTCDATE()函数------

--获取当前日期--
select GETDATE()
--返回：2017-01-10 11:38:10.953

--获取UTC日期---
select GETUTCDATE()
--返回：2017-01-10 03:38:10.950

--计算这两个日期的相差多少小时--
select DATEDIFF(HOUR,GETDATE(),GETUTCDATE())
--返回：-8

5、SYSDATETIME()函数与SYSUTCDATETIME()函数

　　这两个SQL Server 2008函数等价于GETDATE()和GETUTCDATE()函数，但不是返回datetime数据类型的结果，而是返回

SQL Server 2008新的datetime2数据类型的结果，该数据类型可以精确到100纳秒，当然这取决于服务器安装的硬件。

1）SYSDATETIME()函数与SYSUTCDATETIME()函数使用

-------SYSDATETIME()和SYSUTCDATETIME()函数------
--获取系统当前日期---
select SYSDATETIME()
--返回：2017-01-10 11:47:14.5151856

--获取UTC日期--
select SYSUTCDATETIME()
--返回：2017-01-10 03:48:31.1383882

--计算这两个日期的相差多少小时--
select DATEDIFF(HOUR,SYSDATETIME(),SYSUTCDATETIME())
--返回：-8

 6、Day()、Month()和Year()函数

这三个函数分别返回以整数表示的datetime或者smalldatetime类型值的日、月、年。它们的用途很广泛，如可以创建独特的个性化

日期格式。假设需要创建一个自定义的日期值作为字符串，通过将这三个函数的输出结果转换成字符类型，然后进行连接操作，就可以

对输出结果以任何形式进行组合了

1）Day()、Month()和Year(）函数使用

----Day()、Month()和Year()函数----

--获取当前日期的年份--
select Year(GETDATE())
--返回：2017

--获取当前日期的月份--
select MONTH(GETDATE())
--返回：1

--获取当前日期的天数--
select DAY(GETDATE())
--返回：10

--获取当前日期---
select 'Year: '+CONVERT(varchar(4),YEAR(GETDATE()))
+',Month: '+CONVERT(varchar(2),MONTH(GETDATE()))
+',Day: '+ CONVERT(varchar(2),DAY(GETDATE()))
--返回：Year: 2017,Month: 1,Day: 10

三、字符串操纵函数

　　字符串函数可以解析、替换、操纵字符型值。这四个函数是相似的，它们都可以在字符和字符的标准数字表示之间转换。

美国标准信息交换码(American Standard Code for Information Interchange，ASCII)标准字符集包含128个字母、

数字和标点符号。这个字符集是IBM PC体系结构的基础，虽然有些字符现在看来已经很古老了，但还是被保留了下来，

且仍是现代计算机技术的核心。如果在计算机上使用英语，则键盘上的每个字符都是用ASCII码表示的。

1、ASCII()、CHAR()函数

ASCII()和CHAR()是两个基于ASCII的函数，这两个函数可将计算机上应用的每个字符表示为数字。要确定代表一个

字符的数字是什么，就应给ASCII()函数传送只包含一个字符的字符串

1）ASCII()、CHAR()函数的使用

-----获取1-127对应ASCII码--------
--创建一个临时表来保存ASCII码：--
create table #ASCIIVals (ASCIIValue smallint)
--插入数字 0-127到临时表中：--
declare @Number int
set @Number=0
while (@Number<128)
begin
    insert into #ASCIIVals (ASCIIValue) select @Number
    set @Number=@Number+1
end
--查询所有的整型数字与其对应的ACSII码：--
select ASCIIValue,CHAR(AscIIValue) as Chaacter from #ASCIIVals 
--删除临时表--
drop table #ASCIIVals

2、UNICODE()和NCHAR()函数

　　UNICODE()函数是ASCII()的Unicode等价函数，NCHAR()函数和CHAR()函数的功能相同，只不过NCHAR()是用于

Unicode字符的。SQL Server的nchar与nvarchar类型能存储任何Unicode字符，可以和这两个函数一起使用对于特别大

的值，ntext类型和nvarchar(max)类型也支持Unicode字符

1）UniCode()和Nchar()函数使用

---获取字母A对应的Unicode编码--
select UNICODE('A')
--返回：65

--获取数字1对应的Unicode编码--
select UNICODE('1')
--返回：49

--将Unicode编码转换为数字1--
select NCHAR(65)
--返回：A

--将Unicode编码转换为数字1--
select NCHAR(49)
--返回：1

3、CHARINDEX()、PATINDEX()函数

    1）Charindex()函数

CHARINDEX()是原始的SQL函数，用于寻找在一个字符串中某子字符串第一次出现的位置。如函数名所示，

　　这个函数返回一个整型值，表示某子字符串的第一个字符在整个字符串中的位置索引。

SELECT CHARINDEX('sh', 'Washington')　 返回的结果是3，表明s是字符串Washington中的第3个字符。这说明CHARINDEX函数匹配字符的索引

是从1开始的。如果没有匹配到任何结果，函数将返回0。

2）Patindex()函数

　　PATINDEX()函数和CHARINDEXO函数类似，它执行相同的操作，但方法稍许不同，该函数增加了对通配符

 

(即Like运算符中使用的字符)的支持。顾名思义，它将返回一个字符模式的索引。这个函数也可以和ntext、

nchar(max)和nvarchar(max)等大字符类型一起使用。注意，如果和这些大字符类型一起使用，PATINDEX()

函数将返回bigint类型的值，而不是int类型的值。

SELECT PATINDEX('%M_rs%', 'The stars near Mars are far from ours')　　如果想找到一个字符串，在所比较的字符串的前后各有0个或者多个字符，则两个百分符都是必须的。

下划线表明这个位置上的字符不必匹配，它可以是任意字符

 

和使用相同字符串的CHARINDEX()函数作一下比较：

SELECT CHARINDEX('Mars', 'The stars near Mars are far from ours') 

　　这两个函数都返回索引值16。请注意这些函数的执行过程。下一节将把这两个函数和SUBSTRING()函数

 

组合在一起，演示如何使用界定符解析字符串。

3）CharIndex()和PatIndex()函数使用

--返回sh对应的int整型值--
select CHARINDEX('sh','My Name is Shana')
--返回：12

--返回sh对应的bigint类型值--
select PATINDEX('%sh%','My Name is Shana')
--返回：12

4、LEN()函数

　　Len()函数用于返回一个代表字符串长度的整型值。

1）Len()函数使用

--计算字符串的长度--
select LEN('My Name is ShaLi')
--返回：16

5、LEFT()和RIGHT()函数

　　Left()和Right()函数是相似，他们都返回一定长度的子字符串。这两个函数的区别是，它们返回的分别的

不同部分。Left()函数返回字符串最左边的字符，顺序从左到右。Right()函数正好相反，它从最右边的字符开始，

以从右到左的顺序返回特定数量的字符。

1）left()和Right()函数使用

--截取字符串--
select LEFT('My Name is ShaLi',7)
--返回：My Name

--截取字符串--
select RIGHT('My Name is ShaLi',5)
--返回：ShaLi

6、SUBSTRING()函数

　　substring()函数能够从字符串的一个位置开始，从右数若干字符，返回一个特定长度的子字符串。这个函数需要

三个参数：要解析的字符串、起始位置索引、要返回的子字符串长度。如果要返回到所输入字符串尾部的所有字符，

可以使用比所需长度更大的长度值。substring()函数将返工最大长度的字符数，而不会将多出的长度以空格填充。1）Substring()函数的使用

--截取字符串--
select SUBSTRING('My Name is ShaLi',3,5)
--返回：Name

7、LOWER()和UPPER()函数

它们用于将字符串中所有字符分别都转换为小写和大写，这在比较用户输入或者存储用于比较的字符串时是非常有用的。

字符串比较通常是区分大小写的，这取决于SQL Server安装时的设置。如果和其他的字符串操纵函数一起使用，就可以

将字符串转换为合适的大小写，以便存储或显示

1）Lower()和Upper()函数使用

--将字符串NAME转化小写--
select LOWER('NAME')
--返回：name

--将字符串name转化大写--
select UPPER('name')
--返回：NAME

8、LTRIM()和RTRIM()函数

　　这两个函数分别返回将字符串左边或右边空白修剪掉之后的字符串

1）ltrim()和rtrim()函数的使用

--去掉左边空格--
select LTRIM(' _Name')
--返回：_Name

--去掉右边空格--
select RTRIM(' _Name ')
--返回： _Name

9、REPLACE()函数

Replace()函数可以把字符串中的某个字符或某个子字符串替换为另一个字符或者字符串

1）Replace()函数使用

--字符串LiSha替换LiJing--
select REPLACE('My Name is LiSha','LiSha','LiJing')
--返回：My Name is LiJing

10、REPLICATE()和SPACE()函数（填充）

　　将一些字符串重复填充进一个字符串，Replicate()函数填充*号，Space()函数填充空格

1）Replicate()和Space()函数使用

--返回20个字符串，不够用*凑齐--
select 'ZhangSan'+ REPLICATE('*',20-LEN('ZhangSan'))
--返回：ZhangSan************

--SPACE()用法--
select SPACE(20-LEN('ZhangSan'))
--返回：空的

11、REVERSE()函数

　　这个函数将字符串的字符颠倒过来

1）Reverse()函数使用

--将字符串的字符颠倒过来--
select REVERSE('My Name LiNa')
--返回：aNiL emaN yM

12、STUFF()函数

这个函数可将字符串中的一部分替换为另一个字符串。它本质上是将一个字符串以特定的长度插入另一个字符串

中的特定位置上。这对于源值与目的值的长度不一样的字符串替换是很有用的。

1）STUFF()函数使用

--将字符串的金额100.95替换为125.12--
select STUFF('The Book is 100.95.',12,5,'125.12')
--返回：The Book is125.1295.

 

 

 

字符串函数可以解析、替换、操纵字符型值。