Sqlserver常用函数例子说明
在操作SQLServer的时候, 很多时候记不住具体的函数如何使用, 查找联机帮助还是嫌麻烦, 且有很多时候例子也不好懂, 下面对每个常用的函数用用例子说明,一目了然,你自己在数据库中执行一下,结果就知道什么回事了
--字符串功能
--substring
print substring('iamagoodperson',1,5)
select substring('iamagoodperson',1,5)
![](/Images/OutliningIndicators/None.gif)
--upper
select upper('he is a good person')
![](/Images/OutliningIndicators/None.gif)
--lower
select LOWER('this is an VERY interesting job')
![](/Images/OutliningIndicators/None.gif)
--ltrim
select ltrim(' i am a good person')
![](/Images/OutliningIndicators/None.gif)
--rtrim
select rtrim(' heihei,i do not know why it likes this ')
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
--replace
select replace('iwanttoaskyou','ttoa','i love you')
![](/Images/OutliningIndicators/None.gif)
--stuff
select stuff('我的名字是朱旭杰',6,8,'summer')
![](/Images/OutliningIndicators/None.gif)
--Date/Time Fuction
--getdate()
select getdate() as 'today'
![](/Images/OutliningIndicators/None.gif)
--dateadd()
select dateadd(yy,10,getdate())
![](/Images/OutliningIndicators/None.gif)
--datediff()
select datediff(yy,'1982/5/3',getdate()) as
![](/Images/OutliningIndicators/None.gif)
--datepart()
select datepart(dw,getdate())
select datepart(yy,getdate())
select datepart(mm,getdate())
select datepart(dd,getdate())
select datepart(ss,getdate())
select datepart(ms,getdate())
select datepart(dd,'1982/5/3')
print datepart(dw,'1982/8/22')
![](/Images/OutliningIndicators/None.gif)
--day(),相当于datepart(dd,时间)
select day('1982/5/3')
select day(getdate())
--month(),相当于datepart(mm,时间)
select month(getdate())
![](/Images/OutliningIndicators/None.gif)
--year(),相当于datepart(yy,时间)
select year(getdate())
![](/Images/OutliningIndicators/None.gif)
--数学函数
![](/Images/OutliningIndicators/None.gif)
--abs()
select abs(-100.3456)
![](/Images/OutliningIndicators/None.gif)
--sin()
select sin(0.54)
![](/Images/OutliningIndicators/None.gif)
--cos()
select cos(3.14)
![](/Images/OutliningIndicators/None.gif)
--power()
select power(10,2)
![](/Images/OutliningIndicators/None.gif)
--round 返回数字表达式并四舍五入为指定的长度或精度
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
select round(100.45,1)
select round(123,45,-2)
![](/Images/OutliningIndicators/None.gif)
--floor()
select floor(4.9)
select floor(-123.99)
![](/Images/OutliningIndicators/None.gif)
--ceiling()
select ceiling(4.9)
select ceiling(-123.99)
![](/Images/OutliningIndicators/None.gif)
--sqrt()
select sqrt(100)
![](/Images/OutliningIndicators/None.gif)
--square
select square(10)
select square(-15)
![](/Images/OutliningIndicators/None.gif)
--转换函数
--cast()
select cast(100.45 as int)
select cast(1345 as varchar(10))
![](/Images/OutliningIndicators/None.gif)
--convert()
select convert(int,100.56)
select convert(varchar(10),2345)
![](/Images/OutliningIndicators/None.gif)
--空值函数
--isnull()
declare @temp_table table
(
bookID VARCHAR(10) primary key,
book_price float default null,
bookName varchar(50)
)
insert into @temp_table values('1',50,'c#')
insert into @temp_table values('2',null ,'c')
select bookID AS '书的编号',isnull(book_price,0) as '书的价格'
from @temp_table
![](/Images/OutliningIndicators/None.gif)
--nullif(),只要参数里的两个表达式相同就返回null
select nullif('iam','iam')
![](/Images/OutliningIndicators/None.gif)
--coalesce返回其参数中第一个非空表达式
select coalesce(null,null,'i am a good boy')
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
转载请注明出处:撰写人:伍华聪 http://www.iqidi.com