SQL Server 2000 函数学习

cdchenli

　　系统函数：
　　
　　select host_id() host_id, convert(char(13),host_name()) host_name,
suser_id() 'suser_id()', user_id() 'user_id',
convert(char(3),user_name(1)) user_name, convert(char(3),user) 'user',
db_id('master') 'db_id(''master'')', convert(char(9),db_name(2)) 'db_name(2)',
datalength('aaaaaa') 'datalength(''aaaaaaa'')',
datalength(3245) 'datalength(3245)', datalength(getdate()) 'datalength(getdate())';
　　
　　结果：
　　
　　host_idhost_namesuser_id() user_id user_name user db_id('master') db_name(2)
　　3960WWW-ELEAF-ORG NULL 1 dbo dbo 1 tempdb
　　
　　datalength('aaaaaaa') datalength(3245) datalength(getdate())
6 4 8
　　（所影响的行数为 1 行）
　　
　　
　　
　　
　　日期函数datename():
　　
　　declare @var datetime;
select @var = getdate();
select datename(yy,@var) year ,datename(mm,@var) month, datename(dd,@var) day,
datename(hh, @var) hour, datename(mi, @var) minute, datename(ss, @var) second,
datename(ms, @var) millisecond,datename(qq, @var) quarter,
datename(dy, @var) dayOfyear, datename(wk, @var) week,datename(dw, @var) weekday;
　　
　　
　　结果：
　　yearmonthdayhourminutesecondmillisecondquarterdayOfyearweekweekday
　　200503 8 186 38 217 1 67 11 星期二
　　（所影响的行数为 1 行）
　　
　　
　　
　　日期函数datepart():
　　
　　declare @var datetime;
select @var = getdate();
select datepart(yy,@var) year ,datepart(mm,@var) month, datepart(dd,@var) day,
datepart(hh, @var) hour, datepart(mi, @var) minute, datepart(ss, @var) second,
datepart(ms, @var) millisecond,datepart(qq, @var) quarter,
datepart(dy, @var) dayOfyear, datepart(wk, @var) week,datepart(dw, @var) weekday;
　　
　　
　　结果：
　　yearmonthdayhourminutesecondmillisecond quarterdayOfyearweekweekday

200538181138250 167113
　　（所影响的行数为 1 行）
　　
　　
　　日期函数dateadd():
　　
　　select dateadd(yy, 30, getdate()) '30年后', dateadd(mm, 30, getdate()) '30个月后';
　　
　　结果：
　　
　　30年后 30个月后
2035-03-08 18:18:04.6872007-09-08 18:18:04.687
　　（所影响的行数为 1 行）
　　
　　
　　转换函数convert():
　　
　　declare @var datetime
select @var = getdate();
select convert(int, @var) int, convert(varchar(20), @var) varchar, convert(money, @var) money,
convert(real, @var) real, convert(float, @var) float, convert(numeric, @var) numeric,
convert(binary, @var) binary, convert(timestamp, @var) timestamp,
convert(decimal, @var) decimal, convert(bit, @var) bit;
　　
　　
　　结果：
　　int varchar money real float numeric
38418038 20056:39PM38417.777738417.77738417.77768425925838418
　　binary
　　0x000000000000000000000000000000000000000000000000961101339488
　　timestamp decimal bit
　　0x0000961101339488 384181
　　（所影响的行数为 1 行）
　　
　　
　　用convert()函数进行日期格式转换：
　　
　　declare @var datetime;
select @var = getdate();
select convert(char(11),@var,107) '107', convert(char(9), @var, 7) '7',
convert(char(8),@var,108) '108', convert(char(23), @var, 121) '121',
convert(char(19), @var, 120) '120', convert(char(19), @var, 20) '20';
　　
　　结果：
　　
　　107 7 108 121 120 20
----------- --------- -------- ----------------------- ------------------- -------------------
03 09, 2005 03 09, 05 00:41:06 2005-03-09 00:41:06.310 2005-03-09 00:41:06 2005-03-09 00:41:06
　　（所影响的行数为 1 行）
　　
　　
　　IsNull()函数：
　　
　　declare @var int;
select @var = null;
select isNull(@var, 123); //因为@var为空值，所以返回第二个参数。
select @var = 456;
select isNull(@var, 123); //因为@var不为空，所以返回它本身。
　　
　　结果：
　　
　　
-----------
123
　　（所影响的行数为 1 行）
　　
-----------
456
　　（所影响的行数为 1 行）
　　
　　
　　NullIf()函数：
　　
　　declare @var int;
select @var = 15;
select nullIf(@var, 15); //因为两个参数值相同，所以返回空值。
select nullIf(@var, 16); //因为两个参数值不同，所以返回第一个参数本身。

　　
　　结果：
　　
-----------
NULL
　　（所影响的行数为 1 行）
　　
-----------
15
　　（所影响的行数为 1 行）
　　
　　
　　CoalEsce()函数：返回列表中第一个不为空的值。（可以用来过滤空值）
　　
　　declare @a int, @b int, @c int
select @a = null, @b = null, @c = 13;
select coalesce(@a, @b, @c);
select coalesce(null, 15, null);
　　
　　结果：
　　
-----------
13
　　（所影响的行数为 1 行）
　　
-----------
15
　　（所影响的行数为 1 行）
　　
　　
　　
　　字符串函数：
　　
　　declare @a char(15);
select @a = 'bitanstudio2004';
select datalength(@a) datalength, substring(@a, 1, 5) substring,
convert(char(4),(right(@a, 4))) 'right', convert(char(15), upper(@a)) 'upper',
'a' + space(3) + 'b' space, replicate('ab',3) replicate;
　　
select convert(char(12), stuff(@a, 1, 5, '**')) stuff,
convert(char(15), reverse(@a)) reverse,
convert(char(2), ltrim(' ab')) ltrim , convert(char(2),ascii('abcd')) ascii,
convert(char(1), char(67)) char, convert(char(4), str(135.0)) str;
　　
　　结果：
　　
　　datalength substring right upper space replicate
----------- ---------- ----- --------------- ----- ---------
15 bitan 2004 BITANSTUDIO2004 a b ababab
　　（所影响的行数为 1 行）
　　stuff reverse ltrim ascii char str
------------ --------------- ----- ----- ---- ----
**studio2004 4002oidutsnatib ab 97 C
　　（所影响的行数为 1 行）
　　
　　
　　soundex()函数：
　　
　　select soundex('bitan') bitan, soundex('biitaan') biitaan,
soundex('bicaname') bicaname, soundex('biqaname') biqaname;
　　
　　结果：
　　
　　bitan biitaan bicaname biqaname
----- ------- -------- --------
B526 B526 B526 B562
　　（所影响的行数为 1 行）
　　
　　
　　difference()函数：
　　
　　select difference('bitan', 'biqan') 'bitan-biqan',
difference('bitan', 'beetane') 'bitan-beetane',
difference('bitan', 'apple') 'bitan-apple',
difference('bitan', 'biitaan') 'bitan-biitaan';
　　
　　结果：
　　
　　bitan-biqan bitan-beetane bitan-apple bitan-biitaan
----------- ------------- ----------- -------------
3 3 0 4
　　（所影响的行数为 1 行）
　　
　　
　　charindex()函数：
　　
　　declare @var char(20), @var2 char(5);
select @var = 'bbaaaaabbaaaaaab', @var2 = null;
select charindex('aaa', @var, 2), charindex('bb', @var, 3),
charindex('baa', @var), charindex(@var2, @var, 3);
　　
　　结果：
　　
　　
----------- ----------- ----------- -----------
3 8 2 NULL
　　（所影响的行数为 1 行）
　　
　　
　　
　　patindex()函数：(使用正则表达式）
　　
　　declare @var char(30);
select @var = 'bitanstudionowisopen';
select patindex('%tan%', @var) 'tan', patindex('%_s%', @var) '_s',
patindex('%t[a-g]n%', @var) 't[a-g]n', patindex('%o[^a-g]e%', @var) 'o[^a-g]e',
patindex('%t[aeiou]n%', @var) 't[aeiou]n', patindex('%t%n%', @var) 't%n';
　　
　　结果：
　　
　　tan _s t[a-g]n o[^a-g]e t[aeiou]n t%n
----------- ----------- ----------- ----------- ----------- -----------
3 5 3 17 3 3
　　（所影响的行数为 1 行）
　　
　　
　　数学函数：
　　
　　select rtrim(power(2,5)) 'power(2,5)', convert(char(5),
(power(2.00,5))) 'power(2.00,5)', abs(-1) 'abs(-1)',
ceiling(2.5) 'ceiling(2.5)', convert(char(6),degrees(2 * pi())) 'degrees(2 * pi())', floor(3.5) 'floor(3.5)';
select convert(int,rand() * 10) 'rand() * 10',
convert(int, rand(5) * 10) 'rand(5) * 10';
select round(pi(), 0) 'round(pi(), 0)';
select round(pi(), 5) 'round(pi(), 5)';
select round(pi(), 1) 'round(pi(), 1)',sign(-59) 'sign(-59)',sqrt(1.44) 'sqrt(1.44)';
select sqrt(-52) 'sqrt(-52)';
　　
　　结果：
　　
　　power(2,5) power(2.00,5) abs(-1) ceiling(2.5) degrees(2 * pi()) floor(3.5)
------------ ------------- ----------- ------------ ----------------- ----------
32 32.00 1 3 360 3
　　（所影响的行数为 1 行）
　　
　　rand() * 10 rand(5) * 10
----------- ------------
4 7
　　（所影响的行数为 1 行）
　　
　　round(pi(), 0)
---------------------
3.0
　　（所影响的行数为 1 行）
　　
　　round(pi(), 5)
---------------------
3.1415899999999999
　　（所影响的行数为 1 行）
　　
　　round(pi(), 1) sign(-59) sqrt(1.44)
----------------------------------------------------- ----------- -------------
3.1000000000000001 -1 1.2
　　（所影响的行数为 1 行）
　　
　　
　　发生域错误。
sqrt(-52)
-----------------------------------------------------