--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(),相當(dāng)于datepart(dd,時(shí)間)
select day('1982/5/3')
select day(getdate())

--month(),相當(dāng)于datepart(mm,時(shí)間)
select month(getdate())

--year(),相當(dāng)于datepart(yy,時(shí)間)
select year(getdate())

--數(shù)學(xué)函數(shù)

--abs()
select abs(-100.3456)

--sin()
select sin(0.54)

--cos()
select cos(3.14)

--power()
select power(10,2)

--round 返回?cái)?shù)字表達(dá)式并四舍五入為指定的長(zhǎng)度或精度

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)

--轉(zhuǎn)換函數(shù)
--cast()
select cast(100.45 as int)
select cast(1345 as varchar(10))

--convert()
select convert(int,100.56)
select convert(varchar(10),2345)

--空值函數(shù)
--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 '書的編號(hào)',isnull(book_price,0) as '書的價(jià)格'
from @temp_table

--nullif(),只要參數(shù)里的兩個(gè)表達(dá)式相同就返回null
select nullif('iam','iam')

--coalesce返回其參數(shù)中第一個(gè)非空表達(dá)式
select coalesce(null,null,'i am a good boy')