Sqlserver常用函数例子说明
在操作SQLServer的时候, 很多时候记不住具体的函数如何使用, 查找联机帮助还是嫌麻烦, 且有很多时候例子也不好懂, 下面对每个常用的函数用用例子说明,一目了然,你自己在数据库中执行一下,结果就知道什么回事了
--
字符串功能
--
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
'
)
--
字符串功能
--
substring
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
'
)
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
'
)