sql函数记录.docx
《sql函数记录.docx》由会员分享，可在线阅读，更多相关《sql函数记录.docx（25页珍藏版）》请在冰点文库上搜索。
sql函数记录
sql学习：
对变量的赋值不能和数据检索操作同时进行
2/8/10/16进制相互转换：
二进制110,转换为十进制:
0*2^0+1*2^1+1*2^2=6，八进制和十六进制同理，只是把2改为对应的8和16；
把十进制6转换为二进制：
；八进制和十六进制把被除数2改为对应的8和16.
二进制与八进制、十六进制的相互转换可以通过十进制来操作，先转换为十进制，再转换。
十六进制中的A,B,C,D,E,F代表10到15.
配置变量
@@ERROR变量:
转换函数
CAST（）函数:
CAST（）函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。
以下例子用于将文本字符串'123'转换为整型:
SELECTCAST（'123'ASint）
返回值是整型值123。
如果试图将一个代表小数的字符串转换为整型值，又会出现什么情况呢？
SELECTCAST（'123.4'ASint）
CAST（）函数和CONVERT（）函数都不能执行四舍五入或截断操作。
由于123.4不能用int数据类型来表示，所以对这个函数调用将产生一个错误:
Server:
Msg245,Level16,State1,Line1
Syntaxerrorconvertingthevarcharvalue
'123.4'toacolumnofdatatypeint.
在将varchar值'123.4'转换成数据类型int时失败。
要返回一个合法的数值，就必须使用能处理这个值的数据类型。
对于这个例子，存在多个可用的数据类型。
如果通过CAST（）函数将这个值转换为decimal类型，需要首先定义decimal值的精度与小数位数。
在本例中，精度与小数位数分别为9与2。
精度是总的数字位数，包括小数点左边和右边位数的总和。
而小数位数是小数点右边的位数。
这表示本例能够支持的最大的整数值是9999999，而最小的小数是0.01。
SELECTCAST（'123.4'ASdecimal（9,2））
decimal数据类型在结果网格中将显示有效小数位:
123.40
精度和小数位数的默认值分别是18与0。
如果在decimal类型中不提供这两个值，SQLServer将截断数字的小数部分，而不会产生错误。
SELECTCAST（'123.4'ASdecimal）
结果是一个整数值：
123
在表的数据中转换数据类型是很简单的。
下面的例子使用Product表，首先执行如下查询：
SELECTProductNumber,ProductLine,ProductModelID
FROMProduction.Product
WHEREProductSubcategoryID<4
假定产品经理已经创建了一个系统，用于唯一地标识生产出来的每辆自行车，以便跟踪其型号、类型和类别。
他决定合并产品号、产品生产线标识符、产品型号标识符和一个顺序号，为生产出来的每辆自行车创建一个唯一的序列号。
在这个过程的第一步，他要求提供包括除顺序号之外的所有属性的所有可能产品的根标识符。
如果使用下面的表达式，就不能得到希望的结果，如图6-2所示。
SELECTProductNumber
+'-'
+ProductLine
+'-'
+ProductModelIDASBikeSerialNum
FROMProduction.Product
WHEREProductSubcategoryID<4
消息245，级别16，状态1，第1行
在将nvarchar值'BK-R93R-62-R-'转换成数据类型int时失败。
我们没有得到希望的结果，而得到了有点奇怪的错误消息：
请把nvarchar值转换为int。
因为之前我们没有要求进行任何转换，所以这个错误很奇怪。
这个查询的问题在于我们试图利用第一个连接符来连接字符值ProductNumber，利用第二个连接符连接另一个字符值ProductLine，最后连接的是ProductModelID字符值（它是一个整数）。
查询引擎会把连接符当成一个数学运算符，而不是一个字符。
不管结果是什么，都需要更正这个表达式，以确保使用正确的数据类型。
以下表达式执行了必要的类型转换，返回如图6-3所示的结果：
SELECTProductNumber
+'-'
+ProductLine
+'-'
+CAST（ProductModelIDASchar（4））ASBikeSerialNum
FROMProduction.Product
WHEREProductSubcategoryID<4
如果把整型值转换为字符类型就不会增加多余的空格了。
查询引擎将把这些值用加号和连接符组合在一起，进行字符串连接运算，而不是和前面的数值进行加法或者减法运算了。
CONVERT（）函数:
SELECTCONVERT（int,'123'）
SELECTCONVERT（decimal（9,2）,'123.4'）
SELECT'DefaultDate:
'+CONVERT（Varchar（50）,GETDATE（）,100）;
常用：
101-us，103-english/french，104-german.
格式代码0，1和2也可用于数字类型，它们对小数与千位分隔符格式产生影响。
而不同的数据类型所受的影响是不一样的。
一般来说，使用格式代码0（或者不指定这个参数的值），将返回该数据类型最惯用的格式。
使用1或者2通常显示更为详细或者更精确的值。
以下例子使用格式代码0：
DECLARE@NumMoney
SET@Num=1234.56
SELECTCONVERT（varchar（50）,@Num,0）
返回结果如下:
1234.56
使用值1则返回如下结果:
1,234.56
使用值2则返回如下结果:
1234.5600
以下例子和上例相同，但是使用Float类型:
DECLARE@Numfloat
SET@Num=1234.56
SELECTCONVERT（varchar（50）,@Num,2）
使用值0不会改变所提供的格式，但是使用值1或2将返回以科学计数法表示的数字，后者使用了15位小数：
1.23456000000000e+003
STR（）函数:
这是一个将数字转换为字符串的快捷函数。
这个函数有3个参数：
数值、总长度和小数位数。
如果数字的整数位数和小数位数（要加上小数点占用的一个字符）的总和小于总长度，对结果中左边的字符将用空格填充。
在下面第1个例子中，包括小数点在内一共是5个字符。
结果显示在网格中，显然左边的空格被填充了。
这个调用指定，总长度为8个字符，小数位为4位：
SELECTSTR（123.4,8,4）
结果值的右边以0填充：
123.4000。
下面给函数传递了一个10字符的值，并指定结果包含8个字符，有4个小数位：
SELECTSTR（123.456789,8,4）
只有将这个结果截断才能符合要求。
STR（）函数对最后一位进行四舍五入：
123.4568。
现在，如果为函数传递数字1，并指定结果包含6个字符，有4个小数位，STR（）函数将用0补足右边的空位：
SELECTSTR（1,6,4）
1.0000
然而，如果指定的总长度大于整数位数、小数点和小数位数之和，结果值的左边将用空格补齐：
SELECTSTR（1,6,4）
1.0000
SELECTSTR（1,12,4）
----------1.0000
日期函数:
数据类型
输出
time
12:
35:
29.1234567
date
2007-05-08
smalldatetime
2007-05-0812:
35:
00
datetime
2007-05-0812:
35:
29.123
datetime2
2007-05-0812:
35:
29.1234567
datetimeoffset
2007-05-0812:
35:
29.1234567+12:
15
DATEADD（）函数:
DATEADD（）函数用于在日期/时间值上加上日期单位间隔。
比如，要得到2007年4月29日起90天后的日期，可以使用下列语句：
SELECTDATEADD（DAY,90,'4-29-2007'）
结果：
2007-07-2800:
00:
00.000
可以把下表的值作为时间间隔参数传递给DATEADD（）函数。
datepart
缩写
year
yy,yyyy
quarter
qq,q
month
mm,m
dayofyear
dy,y
day
dd,d
week
wk,ww
weekday
dw,w
hour
hh
minute
mi,n
second
ss,s
millisecond
ms
microsecond
mcs
nanosecond
ns
可以将CONVERT（）函数和DATEADD（）函数组合在一起，来对1989年9月8日9个月前的日期值进行格式化。
SELECTCONVERT（varchar（20）,DATEADD（M,-9,'9-8-1989'）,101）
12/08/1988
这将返回一个可变长
DATEDIFF（）函数
同样套用上面dateadd（）的表，
DECLARE@MyBirthDatedatetime
SET@MyBirthDate='7-16-1962'
SELECTDATEDIFF（SS,@MyBirthDate,GETDATE（））
常用的求员工年龄sql语句：
SELECTc.FirstName
c.LastName
e.BirthDate
DATEDIFF（YEAR,e.BirthDate,GETDATE（））ASApproximateAge
FROMHumanResources.Employeeaseinnerjoin
Person.Contactascone.ContactID=c.ContactID
orderbyc.LastName
初看起来结果是对的，但存在的问题是年龄值没有精确到日。
比如，根据表中的数据，Nancy的生日是12月21日，他今年将庆祝第32个生日（这个查询在2010年8月运行）。
如果依据上述计算结果来确定他的年龄何时变化，就应在一月份的某天给他发生日卡片，这比实际日期提前了11个月。
除非用更小的时间单位来计算这些日期的差，否则结果只在雇员实际生日的一年以内是精确的。
以下例子将用差值除以一年（包括闰年）的天数，并将结果值转换为int类型，进行取整运算，而不是四舍五入。
SELECTc.FirstName
c.LastName
e.BirthDate
DATEDIFF（YEAR,e.BirthDate,GETDATE（））ASApproximateAge
CONVERT（int,DATEDIFF（DAY,e.BirthDate,GETDATE（））/365）ASAge
FROMHumanResources.Employeeaseinnerjoin
Person.Contactascone.ContactID=c.ContactID
orderbyc.LastName
比较这次的结果和上一个例子的结果，看看有什么不同。
DATEPART（）与DATENAME（）函数
这两个函数用于返回datetime或者shortdatetime值的日期部分。
DATEPART（）函数返回一个整型值；DATENAME（）函数返回一个包含描述性文字的字符串。
比如，将日期4-29-1988传递给DATEPART（）函数，如指定返回月份值，则返回数字4：
SELECTDATEPART（MONTH,'4-29-1988'）
而使用相同的参数，DATENAME（）函数返回04（这取决于你的机器的本地语言，如果是英文版，那么将返回April）：
SELECTDATENAME（MONTH,'4-29-1988'）
这两个函数都接收和DATEADD（）函数一样的时间间隔参数常量。
GETDATE（）与GETUTCDATE（）函数
这两个函数都用于返回datetime类型的当前日期与时间。
GETUTCDATE（）函数使用服务器上的时区设置来求出UTC时间，这和格林威治标准时间或飞行员所说的"祖鲁时"（ZuluTime）是一样的。
两个函数都能精确到3.33毫秒。
SELECTGETDATE（）
SELECTGETUTCDATE（）
执行这两个函数，都将返回未经格式化的结果，见下图：
我在北京，和UTC时间相差8个小时，和标准时间相差9个小时。
可以使用如下DATEDIFF（）函数来验证这个时间差值：
SELECTDATEDIFF（HOUR,GETDATE（）,GETUTCDATE（））
DAY（）、MONTH（）和YEAR（）函数
这三个函数分别返回以整数表示的datetime或者smalldatetime类型值的日、月、年。
它们的用途很广泛，如可以创建独特的个性化日期格式。
假设需要创建一个自定义的日期值作为字符串，通过将这三个函数的输出结果转换成字符类型，然后进行连接操作，就可以对输出结果以任何形式进行组合了：
SELECT'Year:
'+CONVERT（varchar（4）,YEAR（GETDATE（）））
+',Month:
'+CONVERT（varchar
（2）,MONTH（GETDATE（）））
+',Day:
'+CONVERT（varchar
（2）,DAY（GETDATE（）））
这个脚本生成下列结果：
Year:
2008,Month:
2,Day:
20
字符串操纵函数
ASCII（）、CHAR（）、UNICODE（）和NCHAR（）函数:
ASCII（）和CHAR（）是两个基于ASCII的函数，这两个函数可将计算机上应用的每个字符表示为数字。
要确定代表一个字符的数字是什么，就应给ASCII（）函数传送只包含一个字符的字符串，如下：
SELECTASCII（'A'）
结果是65。
如要将一个已知数字转换为字符，又该怎么办？
使用CHAR（）函数即可：
SELECTCHAR（65）
结果是字母A。
CHARINDEX（）和PATINDEX（）函数
CHARINDEX（）是原始的SQL函数，用于寻找在一个字符串中某子字符串第一次出现的位置。
如函数名所示，这个函数返回一个整型值，表示某子字符串的第一个字符在整个字符串中的位置索引。
以下脚本用于在字符串Washington中寻找子字符串sh的出现位置:
SELECTCHARINDEX（'sh','Washington'）
返回的结果是3，表明s是字符串Washington中的第3个字符。
这说明CHARINDEX函数匹配字符的索引是从1开始的。
如果没有匹配到任何结果，函数将返回0。
在这个例子中使用两个字符作为子字符串并没有特别意义，但是如果字符串包含多个s字符，就有意义了。
PATINDEX（）函数和CHARINDEXO函数类似，它执行相同的操作，但方法稍许不同，该函数增加了对通配符（即Like运算符中使用的字符）的支持,它将返回一个字符模式的索引.
SELECTPATINDEX（'%M_rs%','ThestarsnearMarsarefarfromours'）
注意，如果想找到一个字符串，在所比较的字符串的前后各有0个或者多个字符，则两个百分符都是必须的。
下划线表明这个位置上的字符不必匹配，它可以是任意字符。
和使用相同字符串的CHARINDEX（）函数作一下比较：
SELECTCHARINDEX（'Mars','ThestarsnearMarsarefarfromours'）
这两个函数都返回索引值16。
LEN（）函数
LEN（）函数用于返回一个代表字符串长度的整型值。
这是一个简单、有用的函数，经常与其他函数一起使用，来应用业务规则。
以下例子将月份和日期转换为字符类型，然后测试它们的长度。
如果月份日期只有一个字符，就填充字符0，然后组合成一个8字符的美国格式的日期字符串（MMDDYYYY）。
DECLARE@MonthCharvarchar
（2）,@DayCharvarchar
（2）,@DateOutchar（8）
SET@MonthChar=CAST（MONTH（GETDATE（））ASvarchar
（2））
SET@DayChar=CAST（DAY（GETDATE（））ASvarchar
（2））
--Makesuremonthanddayaretwocharlong:
IFLEN（@MonthChar）=1
SET@MonthChar='0'+@MonthChar
IFLEN（@DayChar）=1
SET@DayChar='0'+@DayChar
--Builddatestring:
SET@DateOut=@MonthChar+@DayChar+CAST（YEAR（GETDATE（））ASchar（4））
SELECT@DateOutASOutputDate
这个脚本将返回代表日期的8个字符:
08152010
LEFT（）和RIGHT（）函数
LEFT（）与RIGHT（）函数是相似的，它们都返回一定长度的子字符串。
这两个函数的区别是，它们返回的分别是字符串的不同部分。
LEFT（）函数返回字符串最左边的字符，顺序从左数到右。
RIGHT（）函数正好相反，它从最右边的字符开始，以从右到左的顺序返回特定数量的字符。
看一看使用这两个函数返回"GeorgeWashington"这个字符串的子字符串的例子。
如果使用LEFT（）函数返回一个5字符的子字符串，则函数先定位最左边的字符，向右数5个字符，然后返回这个子字符串，如下所示。
DECLARE@FullNamevarchar（25）
SET@FullName='GeorgeWashington'
SELECTLEFT（@FullName,5）
结果为：
Georg
如果使用RIGHT（）函数返回一个5字符的子字符串，则函数先定位最右边的字符，向左数5个字符，然后返回这个子字符串，如下所示。
DECLARE@FullNamevarchar（25）
SET@FullName='GeorgeWashington'
SELECTRIGHT（@FullName,5）
结果为：
ngton
要想返回字符串中有意义的部分，这两个函数都不是特别有用。
如果想返回全名中的姓氏或者名字，该怎么办？
这需要多做一点工作。
如果能确定每个姓名中空格的位置，就可以使用LEFT（）函数在全名中读取名字。
在这种情况下，可以使用CHARINDEX（）或者PATINDEX（）函数来定位空格，然后使用LEFT（）函数返回空格前的字符。
下面是第一个用过程方法编写的例子，它将处理过程分解成以下步骤：
DECLARE@FullNamevarchar（25）,@SpaceIndextinyint
SET@FullName='GeorgeWashington'
--Getindexofthedelimitingspace:
SET@SpaceIndex=CHARINDEX（'',@FullName）
--Returnallcharacterstotheleftofthespace:
SELECTLEFT（@FullName,@SpaceIndex-1）
结果为：
George
如果不想在结果中包含空格，就需要从@SpaceIndex值中减去1，这样结果中就只有名字了。
要想返回字符串中有意义的部分，这两个函数都不是特别有用。
如果想返回全名中的姓氏或者名字，该怎么办？
这需要多做一点工作。
如果能确定每个姓名中空格的位置，就可以使用LEFT（）函数在全名中读取名字。
在这种情况下，可以使用CHARINDEX（）或者PATINDEX（）函数来定位空格，然后使用LEFT（）函数返回空格前的字符。
下面是第一个用过程方法编写的例子，它将处理过程分解成以下步骤：
DECLARE@FullNamevarchar（25）,@SpaceIndextinyint
SET@FullName='GeorgeWashington'
--Getindexofthedelimitingspace:
SET@SpaceIndex=CHARINDEX（'',@FullName）
--Returnallcharacterstotheleftofthespace:
SELECTLEFT（@FullName,@SpaceIndex-1）
结果为：
George
如果不想在结果中包含空格，就需要从@SpaceIndex值中减去1，这样结果中就只有名字了。
SUBSTRING（）函数
SUBSTRING（）函数能够从字符串的一个位置开始，往右数若干字符，返回一个特定长度的子字符串。
和LEFT（）函数不同之处是，该函数可以指定从哪个位置开始计数，这样就可以在字符串的任何位置摘取子字符串了。
这个函数需要三个参数：
要解析的字符串、起始位置索引、要返回的子字符串长度。
如果要返回到所输入字符串尾部的所有字符，可以使用比所需长度更大的长度值。
SUBSTRING（）函数将返回最大可能长度的字符数，而不会将多出的长度以空格填充。
只要指定字符串最左边的字符
（1）为起始索引，就可以用SUBSTRING（）函数替代LEFT（）函数。
继续上一节的例子。
可以设置起始位置与长度，返回姓名字符串中间的值。
在这个例子中，从位置4开始，返回一个6字符的子字符串"rgeWa"。
DECLARE@FullNamevarchar（25）
SET@FullName='GeorgeWashington'
SELECTSUBSTRING（@FullName,4,6）
现在将上述各函数组合在一起，即可从名字+空格+姓氏格式的全名字符串中解析出名字和姓氏。
使用先前的逻辑，通过函数嵌套来减少脚本的行数，并去掉@SpaceIndex变量。
下面用SUBSTRING（）函数替代LEFT（）函数：
DECLARE@FullNamevarchar（25）
SET@FullName='GeorgeWashington'
--Returnfirstname:
SELECTSUBSTRING（@FullName,1,CHARINDEX（'',@FullName）-1）
类似的逻辑可以用于解析姓氏，但是必须将起始位置更改为空格后的那个字符。
如果空格在第7个位置上，那么姓氏将从第8个位置开始。
这就意味着起始位置是CHARINDEX（）的返回结果加上1。
DECLARE@FullNamevarchar（25）
SET@FullName='GeorgeWashington'
--Returnlastname:
SELECTSUBSTRING（@FullName,CHARINDEX（'',@FullName）+1,
LEN（@FullName））
把上述步骤组合在一起，就可以运行下面的查询，从全名变量中提取出名字和姓氏：
DECLARE@FullNamevarchar（25）
SET@Ful
升级会员 