来源于:http://www.cnblogs.com/wenanry/archive/2008/02/29/1086618.html
-- 字符串功能
-- substring
print substring ( ' iamagoodperson ' , 1 , 5 )
select substring ( ' iamagoodperson ' , 1 , 5 )
-- upper
select upper ( ' he is a good person ' )
-- lower
select LOWER ( ' this is an VERY interesting job ' )
-- ltrim
select ltrim ( ' i am a good person ' )
-- rtrim
select rtrim ( ' heihei,i do not know why it likes this ' )
-- replace
select replace ( ' iwanttoaskyou ' , ' ttoa ' , ' i love you ' )
-- stuff
select stuff ( ' 我的名字是中国人 ' , 6 , 8 , ' summer ' )
-- Date/Time Fuction
-- getdate()
select getdate () as ' today '
-- dateadd()
select dateadd (yy, 10 , getdate ())
-- datediff()
select datediff (yy, ' 1982/5/3 ' , getdate ()) as
-- 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 ' )
-- day(),相当于datepart(dd,时间)
select day ( ' 1982/5/3 ' )
select day ( getdate ())
-- month(),相当于datepart(mm,时间)
select month ( getdate ())
-- year(),相当于datepart(yy,时间)
select year ( getdate ())
-- 数学函数
-- abs()
select abs ( - 100.3456 )
-- sin()
select sin ( 0.54 )
-- cos()
select cos ( 3.14 )
-- power()
select power ( 10 , 2 )
-- round 返回数字表达式并四舍五入为指定的长度或精度
select round ( 100.45 , 1 )
select round ( 123 , 45 , - 2 )
-- floor()
select floor ( 4.9 )
select floor ( - 123.99 )
-- ceiling()
select ceiling ( 4.9 )
select ceiling ( - 123.99 )
-- sqrt()
select sqrt ( 100 )
-- square
select square ( 10 )
select square ( - 15 )
-- 转换函数
-- cast()
select cast ( 100.45 as int )
select cast ( 1345 as varchar ( 10 ))
-- convert()
select convert ( int , 100.56 )
select convert ( varchar ( 10 ), 2345 )
-- 空值函数
-- 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
-- nullif(),只要参数里的两个表达式相同就返回null
select nullif ( ' iam ' , ' iam ' )
-- coalesce返回其参数中第一个非空表达式
select coalesce ( null , null , ' i am a good boy ' )