SQL Server 2000 函数学习

系统函数：

 

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_id host_name     suser_id() user_id user_name user db_id('master') db_name(2)

3960    WWW-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;

 

 

结果：

year month day hour minute  second millisecond quarter dayOfyear week weekday                       

2005 03    8   18   6       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;

 

 

结果：

year month day hour minute second millisecond quarter dayOfyear week weekday    

2005 3     8   18   11     38     250         1       67        11   3

（所影响的行数为 1 行）

 

 

日期函数dateadd():

 

select dateadd(yy, 30, getdate()) '30年后', dateadd(mm, 30, getdate()) '30个月后';

 

结果：

 

30年后                  30个月后                                                 
2035-03-08 18:18:04.687 2007-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   
38418 03 8 2005 6:39PM 38417.7777 38417.777 38417.777684259258 38418   

binary 

0x000000000000000000000000000000000000000000000000961101339488

timestamp          decimal bit             

0x0000961101339488 38418   1

（所影响的行数为 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)                                            
-----------------------------------------------------